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