Find Out Database Object Dependencies

Some database objects are depending other objects. For example, a view may query other tables or views. We can query user_dependencies to find out these dependencies.

SQL> create view v_x_y as select a.*, b.val val2 from t_x a, t_y b 
where a.id=b.id;

View created.

The following query shows that view v_x_y depends on two tables, t_x and t_y.

SQL>select * from user_dependencies where name='V_X_Y';

NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, 
REFERENCED_LINK_NAME, SCHEMAID, DEPENDENCY_TYPE
V_X_Y VIEW DMUSER T_X TABLE  116 HARD
V_X_Y VIEW DMUSER T_Y TABLE  116 HARD

We create another view v_x_y2 based on view v_x_y.

SQL> create view v_x_y2 as select * from v_x_y;

The following query shows that V_X_Y2 depends on V_X_Y.

SQL> select * from user_dependencies where name='V_X_Y2';

NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, 
REFERENCED_LINK_NAME, SCHEMAID, DEPENDENCY_TYPE
V_X_Y2 VIEW DMUSER V_X_Y VIEW  116 HARD

We create a materialized view based on two tables.

SQL> create materialized view mv_x_y as select a.*, b.val val2 
from t_x a, t_y b where a.id=b.id;
SQL> select * from user_dependencies where name='MV_X_Y';
Materialized view created.

The materialized view MV_X_Y depends on tables T_X ,T_Y and MV_X_Y.

NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, 
REFERENCED_LINK_NAME, SCHEMAID, DEPENDENCY_TYPE
MV_X_Y MATERIALIZED VIEW DMUSER T_X TABLE  116 REF
MV_X_Y MATERIALIZED VIEW DMUSER T_Y TABLE  116 REF
MV_X_Y MATERIALIZED VIEW DMUSER MV_X_Y TABLE  116 REF