6 Useful Oracle Data Dictionary Queries Every DBA Should Have

Table of contents

  1. Tables with number of rows and comments
  2. Views with definition and comments
  3. Table columns details
  4. Foreign keys
  5. Views columns
  6. Tables by number of columns

Introduction

This is a list of SQL queries for Oracle Data Dictionary handy for any Oracle DBA.

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 Selected 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 definition 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

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. Foreign keys

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. 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

Learn more

  1. Data Model vs Data Dictionary vs Database Schema vs ERD
  2. What is Metadata - 9 examples
  3. 10 Ways Data Dictionary Increases Software Developers Productivity

or

Start creating Data Dictionaries for your Oracle databases