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