Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept