Query user_dependencies for reference name : user_dependencies « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. user_dependencies

SQL>
SQL> SELECT name, type, referenced_owner r_owner, referenced_name r_name,
  2         referenced_type r_type
  3  FROM   user_dependencies
  4  WHERE  name IN ('CALL_TEST_PROC', 'TEST_PACK')
  5  OR     referenced_name IN ('CALL_TEST_PROC', 'TEST_PACK');

NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
TEST_PACK                      PACKAGE BODY      SYS
STANDARD
PACKAGE

CALL_TEST_PROC                 PROCEDURE         SYS
SYS_STUB_FOR_PURITY_ANALYSIS
PACKAGE

NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------

TEST_PACK                      PACKAGE BODY      PUBLIC
DBMS_OUTPUT
SYNONYM

TEST_PACK                      PACKAGE BODY      JAVA2S
DBMS_OUTPUT

NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
NON-EXISTENT

CALL_TEST_PROC                 PROCEDURE         JAVA2S
TEST_PACK
PACKAGE

TEST_PACK                      PACKAGE BODY      JAVA2S

NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
TEST_PACK
PACKAGE


6 rows selected.

SQL>