Room With A View

Occasionally the question of which objects reference which table pops up, and it isn't as difficult a question to answer as one might think. Oracle has done a lot of the work for you and has supplied the solution in three views:

DBA_DEPENDENCIES
ALL_DEPENDENCIES
USER_DEPENDENCIES

Looking at the definition of ALL_DEPENDENCIES we see:

SQL> desc all_dependencies
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(17)
 REFERENCED_OWNER                                   VARCHAR2(30)
 REFERENCED_NAME                                    VARCHAR2(64)
 REFERENCED_TYPE                                    VARCHAR2(17)
 REFERENCED_LINK_NAME                               VARCHAR2(128)
 DEPENDENCY_TYPE                                    VARCHAR2(4)

SQL>

[The USER_DEPENDENCIES view differs only in the absence of the OWNER column.]

Notice that we can find pretty much any object that references another object from this view. The query is fairly simple:

SQL> select owner, name
  2  from all_dependencies
  3  where referenced_type = 'TABLE'
  4  and referenced_name = 'HS$_BASE_DD'
  5  /

OWNER                          NAME
------------------------------ ------------------------------
SYS                            HS_ALL_DD
SYS                            HS_INST_DD
SYS                            HS_CLASS_DD
SYS                            DBMS_HS_UTL
SYS                            DBMS_HS_CHK
SYS                            DBMS_HS_ALT
SYS                            DBMS_HS
SYS                            HS_BASE_DD

8 rows selected.

SQL>

Voila! The answer appears with nary a strain on the typing fingers. Simply copy that query into a text editor, change the referenced type (if necessary) and change the referenced_name and you're off to the races. You could even make those two parameters passable and let SQL*Plus prompt you for the values:

select owner, name
     from dba_dependencies
     where referenced_type = upper('&1')
     and referenced_name = upper('&2');

Amazing.

Such information is invaluable when modifications to a table are necessary, as you can track down the packages/procedures/functions/triggers/tables/views referencing the soon-to-be-modified table to allow you to effectively recompile those objects (or re-code the package/procedure/function) to reduce downtime due to a referenced object being marked INVALID.

I tell you, it's pretty slick that Oracle provides the information you need in a form you can use.