List tables used by Materialized Views (MV dependencies) in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-10

Table of Contents:


    Article for: Oracle database SQL Server Azure SQL Database

    Query below lists:

    (A) all referenced tables by materialized views accessible to the current user in Oracle database

    (B) all referenced tables by materialized views in Oracle database

    Query was executed under the Oracle12c Database version.

    Query

    A. Materialized views accessible to the current user

    select owner as view_schema,
           name as view_name,
           referenced_owner as referenced_schema,
           referenced_name as referenced_table
    from sys.all_dependencies
    where type = 'MATERIALIZED VIEW'
          and referenced_type = 'TABLE'
    order by view_schema,
             view_name;
    

    B. If you have privilege on dba_dependencies

    select owner as view_schema,
           name as view_name,
           referenced_owner as referenced_schema,
           referenced_name as referenced_table
    from sys.dba_dependencies
    where type = 'MATERIALIZED VIEW'
          and referenced_type = 'TABLE'
    order by view_schema,
             view_name;
    

    Columns

    • view_schema - view's schema name
    • view_name - view's name
    • referenced_schema - schema of the referenced object
    • referenced_name - name of the referenced object

    Rows

    • One row represents single reference from materialized view to table
    • Scope of rows: represent all references
    • Ordered by view schema and 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