Query to find object dependencies in oracle

Object dependency is important to understand in Oracle. It basically tells the parent child relationship between the objects. Lets understand with an example.

We create one table

CREATE TABLE DEPT_MASTER (
dept_nr NUMBER UNIQUE,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date
);

Right now this table is alone and it is not dependent on any things. Lets create a view over it

SQL> create view dept_vw as select dept_nr,dept_name 
from DEPT_MASTER;

Now this view is dependent on the table. So Dept_master is the parent and view is the child.This relationships helps when we are dropping any object,it can advise what all become invalid.Now in this case if I drop the table , the status of the view will be invalid

Select status from dba_views where view_name='DEPT_VW'
------
VALID

Drop table DEPT_MASTER;
Table Dropped

Select status from dba_views where view_name='DEPT_VW'
------
INVALID

Similarly a package can reference lot of tables, so package is child and Tables are parent.

Now Oracle exposed these dependencies through the view

Column in these are as below with description

So we can use the view depending on the privileges we have .Now lets understand some queries based on these view to find the dependencies of the objects like Query to find object dependencies in oracle

How to Find Parent Level Dependencies

Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where name = UPPER(LTRIM(RTRIM( '&name' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/

Here we query on the Name column so that it can show the parent of this object. This does not include the sys ,system and public objects, if you do want to include them

Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where name = UPPER(LTRIM(RTRIM( '&name' )))
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/

Lets check this with example

CREATE TABLE DEPT_MASTER (
dept_nr NUMBER UNIQUE,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date
);
Table Created

SQL> create view dept_vw as select dept_nr,dept_name from DEPT_MASTER;
View created.

We created the table and view. Now lets see this query on both of these objects

SQL> Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where name = UPPER(LTRIM(RTRIM( '&name' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/
Enter value for name: DEPT_MASTER
old 8: Where name = UPPER(LTRIM(RTRIM( '&name' )))
new 8: Where name = UPPER(LTRIM(RTRIM( 'DEPT_MASTER' )))
no rows selected

So there is no parent of DEPT_MASTER

SQL> Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on  ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where name = UPPER(LTRIM(RTRIM( '&name' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/
Enter value for name: DEPT_MASTER
old 8: Where name = UPPER(LTRIM(RTRIM( '&name' )))
new 8: Where name = UPPER(LTRIM(RTRIM( 'DEPT_VW' )))
------------------------------
VIEW SCOTT.DEPT_VW depends on TABLE SCOTT.DEPT_MASTER

So DEPT_MASTER is the parent of DEPT_VW

How to Find child Level Dependencies

Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( '&refrenced_name' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/

Here we query on the Referenced_name column so that it can show the child of this object. This does not include the sys ,system and public objects, if you do want to include them

Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( '&refrenced_name' )))
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/

Lets check this with example with the same table and view created earliar

SQL> Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( '&referenced_name' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/
Enter value for ref_name: DEPT_MASTER
old 8: Where referenced_name = UPPER(LTRIM(RTRIM( '&Referenced_name' )))
new 8: Where referenced_name = UPPER(LTRIM(RTRIM( 'DEPT_MASTER' )))
OBJECT_DEPENDENCIES
-----------------------------
VIEW SCOTT.DEPT_VW depends on TABLE SCOTT.DEPT_MASTER

How to check both Child and Parent level dependency

SQL> Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on  ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( '&1' )))
or name = UPPER(LTRIM(RTRIM( '&2' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by 1
/

Here we query on both Name and Referenced_name column so that it can show the parent and child of this object. This does not include the sys ,system and public objects, if you do want to include them

Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( '&1' )))
or name = UPPER(LTRIM(RTRIM( '&2' )))
order by 1
/

Lets check this with example

SQL> Select
TYPE || ' ' ||
OWNER || '.' || NAME || ' depends on ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
as OBJECT_DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( '&1' )))
or name = UPPER(LTRIM(RTRIM( '&2' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by 1
/
Enter value for 1: DEPT_MASTER
old 8: Where referenced_name = UPPER(LTRIM(RTRIM( '&1' )))
new 8: Where referenced_name = UPPER(LTRIM(RTRIM( 'DEPT_MASTER' )))
Enter value for 2: DEPT_MASTER
old 9: or name = UPPER(LTRIM(RTRIM( '&2' )))
new 9: or name = UPPER(LTRIM(RTRIM( 'DEPT_MASTER' )))
OBJECT_DEPENDENCIES
-----------------
VIEW SCOTT.DEPT_VW depends on TABLE SCOTT.DEPT_MASTER

Lets see one more example with procedure

create or replace procedure proc1 is
l_count number;
begin
select count(*) into l_count from DEPT_MASTER;
end;
/
create or replace procedure proc2 is
begin
proc1;
end;
/

We create two procedure.Now lets see the dependency

SQL> select name,type,referenced_name,referenced_type
from user_dependencies
connect by prior trim(name) = trim(referenced_name) and
prior trim(type) = trim(referenced_type)
start with referenced_name = '&1'; 
Enter value for 1: DEPT_MASTER
old 5: start with referenced_name = '&1'
new 5: start with referenced_name = 'DEPT_MASTER'

Find the Dependency through DBMS_UTILITY

With 11g onwards, you can find the dependency using DBMS_UTILITY Package also. We have get_dependency subprogram inside the package which can use used for this purpose

Usage

SQL> set serveroutput on
SQL> exec dbms_utility.get_dependency('<TYPE>','<SCHEMA>','<OBJECT_NAME>');

Lets understand this with example of our previous created table,view and package

SQL> set serveroutput on
SQL> exec dbms_utility.get_dependency('VIEW','SCOTT','DEPT_VW');
DEPENDENCIES ON SCOTT.DEPT_VW
--------------------------------
*VIEW SCOTT.DEPT_VW()
PL/SQL procedure successfully completed.

SQL> exec dbms_utility.get_dependency('TABLE','SCOTT','DEPT_MASTER');
DEPENDENCIES ON SCOTT.DEPT_MASTER
----------------------------------
*TABLE SCOTT.DEPT_MASTER()
    VIEW SCOTT.DEPT_VW()
    PROCEDURE SCOTT.PROC1()
       PROCEDURE SCOTT.PROC2()
PL/SQL procedure successfully completed.

SQL> exec dbms_utility.get_dependency('PROCEDURE','SCOTT','PROC1');
DEPENDENCIES ON SCOTT.PROC1
----------------------------
*PROCEDURE SCOTT.PROC1()
   PROCEDURE SCOTT.PROC2()
PL/SQL procedure successfully completed.

I hope you like this information on Query to find object dependencies in oracle and it helps you find something good

Also Reads
Oracle Primary Key
Foreign key in Oracle
how to check trigger status in oracle
v$system_parameter
alter system switch logfile
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_util.htm