Where are the MVIEW dependencies stored?
SQL> create view y1 as select 1 as y1 from dual;
View created.
SQL> create materialized view y2 as select y1 as y2 from y1;
Materialized view created.
SQL> create view y3 as select y2 as y3 from y2;
View created.
SQL> select name, type, referenced_name, referenced_type from user_dependencies where name like 'Y%';
NAME TYPE REFERENCED REFERENCED_TYPE
---------- -------------------- ---------- --------------------
Y1 VIEW DUAL SYNONYM
Y2 MATERIALIZED VIEW Y2 TABLE
Y3 VIEW Y2 TABLE
I can see Y3 depends on Y2, but where can I see that Y2 depends on Y1?
Interestingly things seem ok if Y1 is a table, just not when its a view.
SQL> create view y1 as select 1 as c1 from dual;
View created.
SQL> create materialized view y2 as select c1 as c2 from y1;
Materialized view created.
SQL> create or replace view y3 as select c2 as c3 from y2;
View created.
SQL> select name, type, referenced_name, referenced_type from user_dependencies where name like 'Y%';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
------------------------------ ------------------ ------------------------------ ------------------------------
Y1 VIEW DUAL SYNONYM
Y2 MATERIALIZED VIEW Y2 TABLE
Y3 VIEW Y2 TABLE
3 rows selected.
SQL> create table y1 as select 1 as c1 from dual;
Table created.
SQL> create materialized view y2 as select c1 as c2 from y1;
Materialized view created.
SQL> create or replace view y3 as select c2 as c3 from y2;
View created.
SQL> select name, type, referenced_name, referenced_type from user_dependencies where name like 'Y%';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
------------------------------ ------------------ ------------------------------ ------------------------------
Y2 MATERIALIZED VIEW Y1 TABLE
Y2 MATERIALIZED VIEW Y2 TABLE
Y3 VIEW Y2 TABLE
3 rows selected.
SQL>
I think you'll need to take that up with Support, although the doc for the xxx_DEPENDENCIES does not guarantee that *every* object will be contained there:
"ALL_DEPENDENCIES describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links"