Dependencies for Given Object

Friends some times while trouble shooting or for some analysis we may need the dependency hierarchy of the object, For that we have oracle provided view public_dependency. From where we can extract the details.

SQL> SELECT object_type, owner, object_name
2 FROM dba_objects
3 WHERE object_name = 'PUBLIC_DEPENDENCY';

OBJECT_TYPE OWNER OBJECT_NAME
--------------- --------------- ------------------------------
VIEW SYS PUBLIC_DEPENDENCY
SYNONYM PUBLIC PUBLIC_DEPENDENCY

SQL> desc PUBLIC_DEPENDENCY
Name Null? Type
------------------------------- -------- ----
OBJECT_ID NOT NULL NUMBER
REFERENCED_OBJECT_ID NOT NULL NUMBER

The following query can be used to identify the objects that a given object is dependent upon:

SELECT lvl, u.object_id, u.object_type, LPAD (' ', lvl) || object_name obj
FROM (SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
(SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree,
user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl
/

When prompted, enter the OBJECT_NAME of the OBJECT_TYPE whose dependencies you want to identify.

SQL> SELECT lvl, u.object_id, u.object_type, LPAD (' ', lvl) || object_name obj
2 FROM (SELECT LEVEL lvl, object_id
3 FROM SYS.public_dependency s
4 START WITH s.object_id =
5 (SELECT object_id
6 FROM user_objects
7 WHERE object_name = UPPER ('&OBJECT_NAME')
8 AND object_type = UPPER ('&OBJECT_TYPE'))
9 CONNECT BY s.object_id = PRIOR referenced_object_id
10 GROUP BY LEVEL, object_id) tree,
11 user_objects u
12 WHERE tree.object_id = u.object_id
13 ORDER BY lvl
14 /
Enter value for object_name: XXXX_OJECTS_V
old 7: WHERE object_name = UPPER ('&OBJECT_NAME')
new 7: WHERE object_name = UPPER ('XXXX_OBJECTS_V')
Enter value for object_type: VIEW
old 8: AND object_type = UPPER ('&OBJECT_TYPE'))
new 8: AND object_type = UPPER ('VIEW'))

LVL OBJECT_ID OBJECT_TYPE OBJ
---------- ---------- --------------- ------------------------------
1 2655531 VIEW XXXX_OBJECTS_V
2 2655459 SYNONYM XXXX_OBJECTS
2 2655461 SYNONYM XXXX_OBJECTS_LOG

Another way In the $OH/rdbms/admin exists a UTLDTREE.SQL script which creates a procedure called deptree_fill. This procedure and some tables created with the script make it possible for a user, to see the dependency for objects. Run this script once for each schema that needs this utility. The UTLDTREE.SQL creates a view deptree which will ultimately contain the dependent information for the object. This view is also created as SYS.DEPTREE, the view shows which shared cursors depend on the given object. If the current user is not sys, then this view may get an error, either about lack of privileges or about the non-existence of table x$kglxs. Example -------

SQL> @$ORACLE_HOME/rdbms/admin/utldtree.sql

SQL> SELECT object_type, owner, object_name, status
2 FROM dba_objects
3 WHERE object_name = '&obj_name'
4 /
Enter value for obj_name: XXXX_OBJECTS_V
old 3: WHERE object_name = '&obj_name'
new 3: WHERE object_name = 'XXXX_OBJECTS_V'

OBJECT_TYPE OWNER OBJECT_NAME STATUS
--------------- --------------- ------------------------------ -------
VIEW APPS XXXX_OBJECTS_V VALID
SYNONYM GGL_READONLY XXXX_OBJECTS_V VALID
SYNONYM XXGGLOM XXXX_OBJECTS_V VALID

SQL> EXEC deptree_fill('VIEW','APPS','XXXX_OBJECTS_V');

PL/SQL procedure successfully completed.

SQL> select * from deptree order by seq#;

NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------ -------- -------- ------------------------------ ----------
0 VIEW APPS XXXX_OBJECTS_V 0
1 SYNONYM XXOM XXXX_OBJECTS_V 1
1 PACKAGE APPS XXGGL_OM_EMAIL_BATCH 2
BODY
1 SYNONYM XXREAD XXXX_OBJECTS_V 3
ONLY

This shows the dependency relationship via indenting.

SQL> select * from ideptree;

DEPENDENCIES
--------------------------------------------------------------------------------
SYNONYM XXOM.XXXX_OBJECTS_V
PACKAGE BODY APPS.XXGGL_OM_EMAIL_BATCH
VIEW APPS.XXXX_OBJECTS_V
SYNONYM XXREADONLY.XXXX_OBJECTS_V

define OWNER='SCOTT';
define PARENT_TABLE ='TEST_1' ;

select level,lpad('> ',5*(level-1)) || to_char(child) "TABLE" from 
(select b.table_name "PARENT",a.table_name "CHILD" 
from all_constraints a,all_constraints b 
where a.owner=b.owner 
and a.owner='&OWNER' 
and a.constraint_type='R' 
and a.r_constraint_name=b.constraint_name 
order by b.table_name,a.table_name) 
start with parent='&PARENT_TABLE' 
connect by prior child = parent ;

~~cheers...