Find the Dependent Objects for a table in Oracle

Find the Dependencies for the Objects in Oracle

For finding the dependencies, we used the HR demo schema for showing the example of employee table in Oracle.

Check dependencies for table name

col owner for a20
col name for a20
col type for a10
col referenced_type for 20
col referenced_owner for a20
col referenced_name for a20
select * from dba_dependencies where referenced_owner = '&USER_NAME' and referenced_name = '&OBJECT_NAME';

OR 

Select TYPE || ' ' || OWNER || '.' || NAME || ' references ' ||
REFERENCED_TYPE || ' ' || REFERENCED_OWNER || '.' || REFERENCED_NAME
as DEPENDENCIES From all_dependencies
Where REFERENCED_NAME = UPPER(LTRIM(RTRIM( '&ls_REF_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 ls_ref_name: EMPLOYEES

OUTPUT:
VIEW HR.EMP_DETAILS_VIEW references TABLE HR.EMPLOYEES
TRIGGER HR.SECURE_EMPLOYEES references TABLE HR.EMPLOYEES
TRIGGER HR.UPDATE_JOB_HISTORY references TABLE HR.EMPLOYEES

Check dependencies on procedure, trigger, packages and functions

col owner for a20
col name for a20
col type for a10
col referenced_type for 20
col referenced_owner for a20
col referenced_name for a20
select * from dba_dependencies where owner='&SCHEMA_NAME' and name='&OBJECT_NAME';

OR

Select TYPE || ' ' || OWNER || '.' || NAME || ' references ' ||
REFERENCED_TYPE || ' ' || REFERENCED_OWNER || '.' || REFERENCED_NAME
as DEPENDENCIES From all_dependencies
Where name = UPPER(LTRIM(RTRIM( '&ls_REF_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 ls_ref_name: UPDATE_JOB_HISTORY

OUTPUT:
TRIGGER HR.UPDATE_JOB_HISTORY references PROCEDURE HR.ADD_JOB_HISTORY
TRIGGER HR.UPDATE_JOB_HISTORY references TABLE HR.EMPLOYEES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.