Data Dictionary Queries

Basic database information and configuration (Access: All users)

-- Database version and instance information
-- Access: All users
SELECT * FROM v$version;

-- Database name and instance
SELECT name, instance_name FROM v$database, v$instance;

-- Database creation time
SELECT created FROM v$database;

-- Database size
SELECT 
    (SELECT SUM(bytes)/1024/1024/1024 FROM v$datafile) AS "Datafiles (GB)",
    (SELECT SUM(bytes)/1024/1024/1024 FROM v$logfile) AS "Logfiles (GB)",
    (SELECT SUM(bytes)/1024/1024/1024 FROM v$tempfile) AS "Tempfiles (GB)"
FROM dual;

-- Database character set
SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

-- Database timezone
SELECT dbtimezone FROM dual;

Tablespace monitoring and space usage (Access: DBA or DBA_TABLESPACES privilege)

-- Tablespace usage
-- Access: DBA or users with DBA_TABLESPACES privilege
SELECT 
    tablespace_name,
    file_name,
    bytes/1024/1024 AS "Size (MB)",
    maxbytes/1024/1024 AS "Max Size (MB)",
    autoextensible
FROM dba_data_files
ORDER BY tablespace_name;

-- Tablespace free space
SELECT 
    tablespace_name,
    SUM(bytes)/1024/1024 AS "Free Space (MB)"
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY tablespace_name;

-- Tablespace summary
SELECT 
    df.tablespace_name,
    df.bytes/1024/1024 AS "Total Size (MB)",
    NVL(fs.bytes,0)/1024/1024 AS "Free Space (MB)",
    ROUND((df.bytes - NVL(fs.bytes,0))/df.bytes * 100, 2) AS "Used %"
FROM 
    (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) df,
    (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY df.tablespace_name;

User account and privilege information (Access: DBA or specific privilege views)

-- All users in database
-- Access: DBA or users with DBA_USERS privilege
SELECT 
    username,
    account_status,
    created,
    default_tablespace,
    temporary_tablespace,
    profile
FROM dba_users
ORDER BY created DESC;

-- User privileges
-- Access: DBA or users with DBA_SYS_PRIVS privilege
SELECT 
    grantee,
    privilege,
    admin_option
FROM dba_sys_privs
WHERE grantee = 'USERNAME'
ORDER BY privilege;

-- User roles
-- Access: DBA or users with DBA_ROLE_PRIVS privilege
SELECT 
    grantee,
    granted_role,
    admin_option,
    default_role
FROM dba_role_privs
WHERE grantee = 'USERNAME'
ORDER BY granted_role;

-- User object privileges
-- Access: DBA or users with DBA_TAB_PRIVS privilege
SELECT 
    grantee,
    owner,
    table_name,
    privilege,
    grantable
FROM dba_tab_privs
WHERE grantee = 'USERNAME'
ORDER BY owner, table_name;

Database object information (Access: DBA or specific object privilege views)

-- All tables in database
-- Access: DBA or users with DBA_TABLES privilege
SELECT 
    owner,
    table_name,
    tablespace_name,
    num_rows,
    blocks,
    avg_row_len,
    created,
    last_analyzed
FROM dba_tables
WHERE owner = 'SCHEMA_NAME'
ORDER BY table_name;

-- Table columns
-- Access: DBA or users with DBA_TAB_COLUMNS privilege
SELECT 
    owner,
    table_name,
    column_name,
    data_type,
    data_length,
    nullable,
    data_default
FROM dba_tab_columns
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
ORDER BY column_id;

-- Indexes
-- Access: DBA or users with DBA_INDEXES privilege
SELECT 
    owner,
    index_name,
    table_name,
    index_type,
    uniqueness,
    status,
    tablespace_name
FROM dba_indexes
WHERE owner = 'SCHEMA_NAME'
ORDER BY table_name, index_name;

-- Constraints
-- Access: DBA or users with DBA_CONSTRAINTS privilege
SELECT 
    owner,
    constraint_name,
    table_name,
    constraint_type,
    status,
    search_condition
FROM dba_constraints
WHERE owner = 'SCHEMA_NAME'
ORDER BY table_name, constraint_name;