Find table by name in Oracle database

Ania - Dataedo Team Ania 2018-12-14

Table of Contents:


    Query below lists:

    (A) all tables accessible to the current user in Oracle database having specific name

    (B) all tables in Oracle database having specific name

    Query was executed under the Oracle9i Database version.

    Query

    A. Tables accessible to the current user

    select owner as schema_name,
           table_name
    from sys.all_tables
    -- excluding some Oracle maintained schemas
    where table_name = 'FA_BOOKS'
    and 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',
       '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', 'WKSYS')
    order by owner,
             table_name;
    

    B. If you have privilege on dba_tables

    select owner as schema_name,
           table_name
    from sys.dba_tables
    -- excluding some Oracle maintained schemas
    where table_name = 'FA_BOOKS'
    and 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', 
       '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', 'WKSYS')
    order by owner,
             table_name;
    

    Columns

    • schema_name - schema name, owner of the table
    • table_name - name of the table

    Rows

    • One row represents one table having specific name
    • Scope of rows: (A) all tables accessible to the current user in Oracle database having specific name, (B) all tables in Oracle database having specific name
    • Ordered by schema name, table_name

    Sample results

    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