6 Useful Oracle Data Dictionary Queries Every DBA Should Have

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

By Use Case

By Industry

Learn About Dataedo

Learn About Data

After Hours