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. Let’s 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. Let’s 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 relationship 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 a lot of tables, so the package is child and Tables are parents.

Now Oracle exposed these dependencies through the view

Column in these areas below with the description

So we can use the view depending on the privileges we have. Now let’s understand some queries based on these views 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
/

Let’s check this with an 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 let’s 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
/

Let’s check this with an example with the same table and view created earlier

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
/

Let’s check this with an 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

Let’s 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 procedures. Now let’s 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

From 11g onwards, you can find the dependency using the 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>');

Let’s understand this with an example of our previously 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 : primary key in oracle uniquely identify the row in the table. It cannot be null & can be created at the time of table creation or after the table is created
Foreign key in Oracle : Foreign Key in Oracle is to enforce data integrity between multiple tables. It can be added at the time of table creation and after the table is created also.
how to check trigger status in oracle : Check out How to check Trigger status in Oracle, how to enable/disable the trigger, how to find the trigger definition, how to find all the triggers
v$system_parameter : This post covers the Difference between v$system_parameter and v$parameter and other parameter views available in Oracle database.
alter system switch logfile: This post covers alter system switch logfile , alter system archive log current command,how to use them and what all they performs in the background
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_util.htm