List tables used by a view in Oracle database

Ania - Dataedo Team Ania 2018-11-26

Table of Contents:


    Query below lists:

    (A) all objects used in views accessible to the current user in Oracle database

    (B) all objects used in views in Oracle database

    Query was executed under the Oracle9i Database version.

    Query

    A. Views accessible to the current user

    select owner as schema_name,
           name as view_name,
           referenced_owner as referenced_schema_name,
           referenced_name,
           referenced_type
    from sys.all_dependencies
    where type = 'VIEW'
    -- excluding some Oracle maintained schemas
    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','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 owner, name, referenced_name, referenced_owner, referenced_type;
    

    B. If you have privilege on dba_dependencies

    select owner as schema_name,
           name as view_name,
           referenced_owner as referenced_schema_name,
           referenced_name,
           referenced_type
    from sys.dba_dependencies
    where type = 'VIEW'
    -- excluding some Oracle maintained schemas
    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','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 owner, name, referenced_name, referenced_owner, referenced_type;
    

    Columns

    • schema_name - schema name
    • view_name - view name
    • referenced_schema_name - schema of the referenced object
    • referenced_name - name of the referenced object
    • referenced_type - type of the referenced object

    Rows

    • One row represents one object used in a specific view in a database
    • Scope of rows: (A) all objects used by the views accessible to the current user in Oracle database, (B) all objects used by views in Oracle database
    • Ordered by schema name, view name, referenced object's name, referendced schema name, type of the referenced object

    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