How to Check / Find the Oracle Table Depended Objects?

advertisements

_____________________________________________________________________________________________________________________

There are two ways to find out the depended objects in Oracle.

First method you can use user_dependencies/ dba_dependencies view

Secondly, you can use deptree table to find out the details.

SQL> CREATE VIEW V_EMP AS SELECT EMP_NAME FROM EMP;

View created.

SQL> CREATE SYNONYM SYN_EMP FOR EMP;

Synonym created.

SQL>

create or replace procedure pr_sal is

cursor c1 is select emp_name from emp;

begin

for rec1 in c1 loop

dbms_output.put_line(rec1.emp_name);

end loop;

end;

Procedure created.

SQL> select name, TYPE, REFERENCED_NAME, REFERENCED_TYPE

from user_dependencies where REFERENCED_NAME='EMP';

NAME TYPE REFERENCED REFERENCED_TYPE

---------- ------------------ ---------- ------------------

PR_SAL PROCEDURE EMP TABLE

SYN_EMP SYNONYM EMP TABLE

V_EMP VIEW EMP TABLE

For this you have to run /rdbms/admin/utldtree.sql package to create required table and views

SQL> exec deptree_fill(type =>'TABLE',schema => 'SCOTT',name => 'EMP');

Now you can select the dependency data from deptree table. NESTED_LEVEL column gives the details the object is directly depended on the main object. Here in below example SYNO_EMP object is a synonym for SYN_EMP. That is why the nested_level shows 2 for this object.

SQL> select * from deptree order by seq#;

NESTED_LEVEL TYPE SCHEMA NAME SEQ#

------------ ------------------- ------------------------------ ---------- ----------

0 TABLE SCOTT EMP 0

1 SYNONYM SCOTT SYN_EMP 1

2 SYNONYM SCOTT SYNO_EMP 2

1 VIEW SCOTT V_EMP 3

1 PROCEDURE SCOTT PR_SAL 4