December 21st, 2003, 11:53 PM
Base tables for materialized view
When creating a materialized view, the user does mention the query that will be responsible for populating the materialized view on refresh. For example,CREATE MATERIALIZED VIEW TEST_QUERY REFRESH ON COMMIT AS SELECT A.NUM AS MAT_COLUMN_1, B.DES AS MAT_COLUMN_2 FROM BASE_TABLE_1 A, BASE_TABLE_2 B. QUESTION: In the sys.snap$ table though there is a mention of 2 tables being referenced, only one table is actually stored in the column 'master'. I need to be able to find all the base tables referred in the query. Also if I take another approach and access the view dba_mview_detail_relations, I do get the information of all the base tables present in the query BUT then this view does not HAVE information about the materialized view created in SYS schema. I need to know all the base tables that are referred irrespective of the materialized view being created in any schema.