List tables in Oracle database

Queries below list tables in Oracle database from all schemas you have an access to. If you'd like to list tables in specific schema use this query.

We will exclude some Oracle mantained schemas that were created, and are maintained, by Oracle-supplied scripts.

Query

A. Generic query

select t.owner as schema_name, 
    t.object_name as table_name
from all_objects t
where t.object_type = 'TABLE'
   -- we exclude some Oracle maintained schemas
   and t.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', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by t.owner, 
    t.object_name 

B. If you have DBA role in the database

select t.owner as schema_name, 
   t.object_name as table_name
from dba_objects t
where t.object_type = 'TABLE'
   -- we exclude some Oracle maintained schemas
   and t.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', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by t.owner, 
   t.object_name   

Columns

  • schema_name - schema name
  • table_name - table name

Rows

  • One row represents one table
  • Scope of rows: all tables in the database you have access to
  • Ordered by schema and table name

Notes

  • To see tables in OTHER SCHEMAS you have to be granted one of these privileges:
    • SELECT ANY DICTIONARY
    • (SELECT | INSERT | UPDATE | DELETE) ANY TABLE
    • DBA role
  • To use query B you need to have DBA role in the database

Sample results

Result shows list of tables in database

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

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