Check the table, procedure, views dependences in Oracle

Check the dependences on the table in Oracle

select 
TYPE || ' ' ||
OWNER || '.' || NAME ||
' -----DEPENDENCIES ON-----> ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
 as DEPENDENCIES
From all_dependencies
Where (OWNER = 'HR' OR REFERENCED_OWNER = 'HR' )
AND (name = 'EMPLOYEES' or REFERENCED_NAME = 'EMPLOYEES')
 AND ( REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by REFERENCED_OWNER, REFERENCED_NAME,type ;

OUTPUT
DEPENDENCIES
--------------------------------
TRIGGER HR.SECURE_EMPLOYEES -----DEPENDENCIES ON-----> TABLE HR.EMPLOYEES
TRIGGER HR.UPDATE_JOB_HISTORY -----DEPENDENCIES ON-----> TABLE HR.EMPLOYEES
VIEW HR.EMP_DETAILS_VIEW -----DEPENDENCIES ON-----> TABLE HR.EMPLOYEES

Check all the schema dependencies in Oracle

select 
TYPE || ' ' ||
OWNER || '.' || NAME ||' -----DEPENDENCIES ON-----> ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME as DEPENDENCIES
From all_dependencies
Where (OWNER = 'HR' OR REFERENCED_OWNER = 'HR' )
 AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
)
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
)
order by REFERENCED_OWNER, REFERENCED_NAME,type ;


Output:
DEPENDENCIES
---------------------------------------------------------------------------
VIEW HR.EMP_DETAILS_VIEW -----DEPENDENCIES ON-----> TABLE HR.COUNTRIES
VIEW HR.EMP_DETAILS_VIEW -----DEPENDENCIES ON-----> TABLE HR.DEPARTMENTS
TRIGGER HR.UPDATE_JOB_HISTORY -----DEPENDENCIES ON-----> TABLE HR.EMPLOYEES
TRIGGER HR.SECURE_EMPLOYEES -----DEPENDENCIES ON-----> TABLE HR.EMPLOYEES

Find dependence with package dbms_utility.

-- For Table
exec dbms_utility.get_dependency('TABLE','HR','EMPLOYEES');

DEPENDENCIES ON HR.EMPLOYEES
------------------------------------------------------------------
*TABLE HR.EMPLOYEES()
*   VIEW HR.EMP_DETAILS_VIEW()
*   TRIGGER HR.SECURE_EMPLOYEES()
*   TRIGGER HR.UPDATE_JOB_HISTORY()

--For Procedure
SQL> exec dbms_utility.get_dependency('PROCEDURE','HR','ADD_JOB_HISTORY');

DEPENDENCIES ON HR.ADD_JOB_HISTORY
------------------------------------------------------------------
*PROCEDURE HR.ADD_JOB_HISTORY()
*   TRIGGER HR.UPDATE_JOB_HISTORY()


--For View
SQL> exec dbms_utility.get_dependency('VIEW','HR','EMP_DETAILS_VIEW');

DEPENDENCIES ON HR.EMP_DETAILS_VIEW
------------------------------------------------------------------
*VIEW HR.EMP_DETAILS_VIEW()

--For SEQUENCE
SQL> exec dbms_utility.get_dependency('SEQUENCE','HR','DEPARTMENTS_SEQ');
-
DEPENDENCIES ON HR.DEPARTMENTS_SEQ
------------------------------------------------------------------
*SEQUENCE HR.DEPARTMENTS_SEQ()