Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Duke.

Asked: August 05, 2016 - 6:50 pm UTC

Last updated: August 07, 2016 - 8:54 am UTC

Version: 12.1.0.1.0

Viewed 1000+ times

You Asked

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?

and Connor said...

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"



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library