query to get all tables in a materialized view

Question

Good afternoon friends,

a query is there any way (select * from) to visualize which tables form a materialized view? ex:

CREATE MATERIALIZED VIEW table_vm
 
  REFRESH COMPLETE ON COMMIT
  as
SELECT * FROM table1;
UNION ALL
SELECT * FROM table2;

I would like to output something like this:

view name | table name table_m | Table 1 table_m | table 2 tabla_m | table 3 .... ....

Thank you so much,

I would appreciate any information.

Answer 1

You can use the view DBA_DEPENDENCIES to view any dependencies for an object compiled into the database. Querying that view with the name of your materialized view should list all of the tables as well as any other dependencies the materialized view relies on. REFERENCED_OWNER and REFERENCED_NAME will hold the values of the tables being used by the materialized view.

SELECT *
  FROM dba_dependencies
 WHERE owner = 'OWNER_OF_MV' AND name = 'TABLE_MV';