6 Useful Oracle Data Dictionary Queries Every DBA Should Have

Table of Contents:


    This is a list of handy SQL queries to the Oracle database data dictionary. You can also find many more useful queries here.

    Excluding standard Oracle schemas

    Prior Oracle 12c

    We excluded common Oracle schemas for you to be able to see user objects only, but we don't guarantee those are all schemas you should exclude.

     where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
    

    Oracle 12c and newer

    Version 12c introduced new useful field: oracle_maintained, which specifies whether the user was created, and is maintained, by Oracle-supplied scripts. You can replace where clause above with this line:

    where tab.owner in 
    (select username from all_users where oracle_maintained = 'N')
    

    Including specific schema only

    If you want to list objects for specific schema only, uncomment this line and provide your schema name.

    --- and tab.owner = 'HR' 
    

    ALL_ vs DBA_ views

    Oracle has 3 sets of views, varying with prefix and their application:

    • USER_ - include objects owned by the current user only, not suitable in this case
    • ALL_ - include objects all objects (from all schemas) accessible to the current user
    • DBA_ - includes all objects, but requires DBA role

    We chose to use ALL_ views so not only DBAs can use those scripts. If you are a DBA you will view all objects anyway.

    If you have DBA role in a database you may replace all_ view prefixes with DBA_.

    1. Tables with number of rows and comments

    This query returns a list of tables in schemas sorted by name, with comments and number of rows in each table.

    Query

    select tab.owner as schema_name,
           tab.table_name as table_name,
           obj.created,
           obj.last_ddl_time as last_modified,       
           tab.num_rows,
           tab.last_analyzed,
           comm.comments
      from all_tables tab
           inner join all_objects obj
               on obj.owner = tab.owner
              and obj.object_name = tab.table_name
           left outer join all_tab_comments comm
               on tab.table_name = comm.table_name
              and tab.owner = comm.owner
     where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
     --  and tab.owner = 'HR' 
     order by tab.owner, 
           tab.table_name;
    

    Rows

    One row represents one table. All tables will be included.

    Columns

    Column Meaning
    SCHEMA_NAME Schema name
    TABLE_NAME Table name
    CREATED Table creation date and time
    LAST_MODIFIED Table last modification date and time
    NUM_ROWS Number of rows in the table
    LAST_ANALYZED Table data last analyzed date and time
    COMMENTS Table comments

    Sample results

    2. Views with their definition script and comments

    This query returns a list of database views with their definition SQL and a comments.

    Query

    select obj.owner as schema_name,
           obj.object_name as view_name,
           obj.created,
           obj.last_ddl_time as last_modified,
           def.text as definition,
           comm.comments
      from all_objects obj
           left outer join all_views def
               on obj.owner = def.owner
              and obj.object_name = def.view_name
           left outer join all_tab_comments comm
               on obj.object_name = comm.table_name
              and obj.owner = comm.owner
     where obj.object_type = 'VIEW'
       and obj.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
         --and obj.owner = 'HR'
     order by obj.owner, 
            obj.object_name;
    

    Rows

    One row represents one view.

    Columns

    Column Meaning
    SCHEMA_NAME Schema name
    VIEW_NAME View name
    CREATED View creation date and time
    LAST_MODIFIED View last modification date and time
    DEFINITION View definition (SQL query)
    COMMENTS View comments

    Sample results

    3. Table columns details (table structure)

    This query returns a list of tables and their columns with details.

    Query

    select col.owner as schema_name,
           col.table_name, 
           col.column_name, 
           col.data_type,
           decode(char_length, 
                  0, data_type,
                  data_type || '(' || char_length || ')') as data_type_ext,
           col.data_length, 
           col.data_precision,  
           col.data_scale,  
           col.nullable, 
           col.data_default as default_value,
           nvl(pk.primary_key, ' ') as primary_key, 
           nvl(fk.foreign_key, ' ') as foreign_key, 
           nvl(uk.unique_key, ' ') as unique_key, 
           nvl(check_const.check_constraint, ' ') check_constraint,
           comm.comments
      from all_tables tab
           inner join all_tab_columns col 
               on col.owner = tab.owner 
              and col.table_name = tab.table_name          
           left join all_col_comments comm
               on col.owner = comm.owner
              and col.table_name = comm.table_name 
              and col.column_name = comm.column_name 
           left join (select constr.owner, 
                             col_const.table_name, 
                             col_const.column_name, 
                             'PK' primary_key
                        from all_constraints constr 
                             inner join all_cons_columns col_const
                                 on constr.constraint_name = col_const.constraint_name 
                                and col_const.owner = constr.owner
                       where constr.constraint_type = 'P') pk
               on col.table_name = pk.table_name 
              and col.column_name = pk.column_name
              and col.owner = pk.owner
           left join (select constr.owner, 
                             col_const.table_name, 
                             col_const.column_name, 
                             'FK' foreign_key
                        from all_constraints constr
                             inner join all_cons_columns col_const
                                 on constr.constraint_name = col_const.constraint_name 
                                and col_const.owner = constr.owner 
                       where constr.constraint_type = 'R'
                       group by constr.owner, 
                                col_const.table_name, 
                                col_const.column_name) fk
               on col.table_name = fk.table_name 
              and col.column_name = fk.column_name
              and col.owner = fk.owner
           left join (select constr.owner, 
                             col_const.table_name, 
                             col_const.column_name, 
                             'UK' unique_key
                        from all_constraints constr
                             inner join all_cons_columns col_const
                                 on constr.constraint_name = col_const.constraint_name 
                                and constr.owner = col_const.owner
                       where constr.constraint_type = 'U' 
                       union
                      select ind.owner, 
                             col_ind.table_name, 
                             col_ind.column_name, 
                             'UK' unique_key
                        from all_indexes ind
                             inner join all_ind_columns col_ind 
                                on ind.index_name = col_ind.index_name                  
                       where ind.uniqueness = 'UNIQUE') uk
               on col.table_name = uk.table_name 
              and col.column_name = uk.column_name
              and col.owner = uk.owner
           left join (select constr.owner, 
                             col_const.table_name, 
                             col_const.column_name, 
                             'Check' check_constraint
                        from all_constraints constr 
                             inner join all_cons_columns col_const
                                 on constr.constraint_name = col_const.constraint_name 
                                and col_const.owner = constr.owner
                       where constr.constraint_type = 'C'
                       group by constr.owner, 
                             col_const.table_name, 
                             col_const.column_name) check_const
               on col.table_name = check_const.table_name 
              and col.column_name = check_const.column_name      
              and col.owner = check_const.owner
     where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
     --  and col.owner = 'HR' 
     --  and lower(tab.table_name) like '%'   
     order by col.owner,
           col.table_name, 
           col.column_name;
    

    Rows

    One row represents one table column.

    Columns

    Column Meaning
    SCHEMA_NAME Schema name
    TABLE_NAME Table name
    COLUMN_NAME Column name
    DATA_TYPE Data type. For instance, VARCHAR or NUMBER
    DATA_TYPE_EXT Data type with string length. For instance, VARCHAR(100) or NUMBER
    DATA_LENGTH Length of the column in bytes.
    DATA_PRECISION Decimal precision for NUMBER datatype, binary precision for FLOAT datatype, null for all other datatypes
    DATA_SCALE Digits to right of decimal point in a number.
    NULLABLE Nullable flag. "Y" if column is nullable, "N" if column is not nullable.
    DEFAULT_VALUE Column default value
    PRIMARY_KEY Primary key flag. "PK" when column is part of table primary key.
    FOREIGN_KEY Foreign key flag. "FK" when column is part of foreign key.
    UNIQUE_KEY Unique key flag. "UK" when column is part of unique key.
    CHECK_CONSTRAINT Check constraint flag. "Check" when column is part of check constraint.
    COMMENTS Column comments

    Sample results

    4. List foreign keys in Oracle

    This query returns a list of tables and their constraints.

    Query

    select foreign_table.owner as table_schema_name,
           foreign_table.table_name, 
           foreign_table.column_name,
           constr.constraint_name,
           primary_table.owner as primary_table_schema_name,
           primary_table.table_name as primary_table_name,  
           primary_table.column_name  as primary_table_column,
           foreign_table.table_name || '.' || 
             foreign_table.column_name || ' = ' || 
             primary_table.table_name  || '.' || 
             primary_table.column_name as join_condition
      from all_constraints constr 
           inner join all_cons_columns foreign_table
               on foreign_table.owner = constr.owner
              and foreign_table.constraint_name = constr.constraint_name
           inner join all_cons_columns primary_table 
               on primary_table.constraint_name = constr.r_constraint_name
              and primary_table.owner = constr.r_owner
              and primary_table.position = foreign_table.position
     where constr.r_owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
       -- and constr.r_owner = 'HR'
       and lower(foreign_table.table_name) like '%'   
     order by foreign_table.table_name,
           foreign_table.column_name;
    

    Rows

    One row represents one pair of columns in foreign key.

    Columns

    Column Meaning
    TABLE_SCHEMA_NAME Foreign table schema name
    TABLE_NAME Foreign table name
    COLUMN_NAME Foreign table column name
    CONSTRAINT_NAME Constraint name
    PRIMARY_TABLE_SCHEMA_NAME Primary table schema name
    PRIMARY_TABLE_NAME Primary table name
    PRIMARY_TABLE_COLUMN Primary table column name
    JOIN_CONDITION Join condition containing foreign and primary key tables and columns.

    Sample results

    5. List all views columns

    This query returns a list of views with their columns.

    Query

    select col.table_name as view_name,
           col.column_name,
           col.data_type,
           decode(char_length, 
                  0, data_type,
                  data_type || '(' || char_length || ')') as data_type_ext,
           col.data_length,
           col.data_precision,
           col.data_scale,
           col.nullable,
           nvl(comm.comments,' ') as comments
      from all_views v 
           inner join all_tab_columns col
               on v.view_name = col.table_name
           inner join all_col_comments comm
               on col.table_name = comm.table_name 
              and col.owner = comm.owner
              and col.column_name = comm.column_name 
     where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
    --   and col.owner = 'HR'   
     order by col.table_name, 
           col.column_name;
    

    Rows

    One row represents one view column.

    Columns

    Column Meaning
    VIEW_NAME View name
    COLUMN_NAME View column name
    DATA_TYPE Data type. For instance, VARCHAR or NUMBER
    DATA_TYPE_EXT Data type with string length. For instance, VARCHAR(100) or NUMBER
    DATA_LENGTH Length of the column in bytes.
    DATA_PRECISION Decimal precision for NUMBER datatype, binary precision for FLOAT datatype, null for all other datatypes
    DATA_SCALE Digits to right of decimal point in a number.
    NULLABLE Nullable flag. "Y" if column is nullable, "N" if column is not nullable.
    COMMENTS Column comments

    Sample results

    6. Tables by number of columns

    This query returns a list of tables sorted by the number of columns they contain.

    Query

    select col.owner as schema_name,
           col.table_name, 
           count(*) columns
      from all_tab_columns col 
           inner join all_tables tab
               on col.owner = tab.owner
              and col.table_name = tab.table_name
     where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
     'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
     'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
     'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
     'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
     'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
     'XS$NULL','PUBLIC')
    --   and col.owner = 'HR'
     group by col.owner,
           col.table_name
     order by count(*) desc;
    

    Rows

    One row represents one table.

    Columns

    Column Meaning
    SCHEMA_NAME Schema name
    TABLE_NAME Table name
    COLUMNS Number of columns table contains

    Sample results

    0
    There are no comments. Click here to write the first comment.