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