Oracle PL/SQL/System Tables Views/dba dependencies
Query dba_dependencies
<source lang="sql">
SQL> SQL> set linesize 132 SQL> column referenced_object format a35 SQL> clear breaks breaks cleared SQL> break on name on type SQL> SQL> select name,type,referenced_owner ||"."|| referenced_name referenced_object
2 from dba_dependencies 3 where owner = "HR" 4 /
NAME TYPE REFERENCED_OBJECT
----------------- -----------------------------------
UPDATE_JOB_HISTORY TRIGGER SYS.STANDARD ADD_JOB_HISTORY PROCEDURE SYS.STANDARD SECURE_DML PROCEDURE SYS.STANDARD
SYS.DBMS_STANDARD
ADD_JOB_HISTORY PROCEDURE SYS.SYS_STUB_FOR_PURITY_ANALYSIS SECURE_DML PROCEDURE SYS.SYS_STUB_FOR_PURITY_ANALYSIS EMP_DETAILS_VIEW VIEW HR.REGIONS
HR.COUNTRIES HR.LOCATIONS HR.DEPARTMENTS HR.JOBS
UPDATE_JOB_HISTORY TRIGGER HR.EMPLOYEES SECURE_EMPLOYEES TRIGGER HR.EMPLOYEES EMP_DETAILS_VIEW VIEW HR.EMPLOYEES ADD_JOB_HISTORY PROCEDURE HR.JOB_HISTORY SECURE_EMPLOYEES TRIGGER HR.SECURE_DML UPDATE_JOB_HISTORY TRIGGER HR.ADD_JOB_HISTORY 17 rows selected. SQL> --
</source>
Query dba_dependencies by owner and referenced_name
<source lang="sql">
SQL> SQL> SQL> select owner || "." || name name, type, dependency_type
2 from dba_dependencies 3 where referenced_owner = "HR" and referenced_name = "EMPLOYEES" 4 /
NAME TYPE DEPE
----------------- ----
HR.EMP_DETAILS_VIEW VIEW HARD HR.SECURE_EMPLOYEES TRIGGER HARD HR.UPDATE_JOB_HISTORY TRIGGER HARD 3 rows selected. SQL> SQL> --
</source>