Finding Object Dependencies

Many objects such as stored programs and views in the database are based on other objects either in the same database or on a remote database. It will be good to know the dependencies of a table, that is, what other objects make use of the table, before you get rid of it...

The DBA_DEPENDENCIES (or ALL_DEPENDENCIES or USER_DEPENDENCIES) view provides the information you need. 

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

The view has the Object owner , name and type and parent or referenced owner, name, type. The REFERENCED_LINK_NAME shows if the object is from a remote database. The DEPENDENCY_TYPE is a new column added to this view in 8i. This column shows the dependency type is "HARD" or "REF". All the object dependencies in Oracle7 are "HARD", since we did not have the REF data type.

Here is an example to find the objects that reference the objects from another database using database link PROD1.

SQL> select OWNER, NAME, TYPE,
  2         REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
  3  from   DBA_DEPENDENCIES
  4  where  REFERENCED_LINK_NAME = 'PROD1.WORLD'
  5  order by OWNER, NAME;

OWNER                NAME                   TYPE
-------------------- ---------------------- ------------
REFERENCED_OWNER     REFERENCED_NAME        REFERENCED_T
-------------------- ---------------------- ------------
SCOTT                SCOT_PACK_CUSTOM       PACKAGE BODY
BILL                 ASSET_TABLE            TABLE

SCOTT                SCOT_PACK_CUSTOM       PACKAGE BODY
BIJU                 CO_OWN_VIEW            VIEW

SCOTT                SCOT_PROC_EXPORTS      PROCEDURE
BILL                 ASSET_TABLE            TABLE

The results show that SCOTT owned package SCOT_PACK_CUSTOM reference the table ASSET_TABLE owned by BILL and a view CO_OWN_VIEW owned by BIJU.

Another example, shows the objects that are referred in the package SCOT_PACK_CUSTOM owned by SCOTT.

SQL> select REFERENCED_OWNER R_OWNER, REFERENCED_NAME, 
  2         REFERENCED_TYPE R_TYPE, REFERENCED_LINK_NAME R_LINK
  3  from   DBA_DEPENDENCIES
  4  where  NAME = 'SCOT_PACK_CUSTOM'
  5  and    OWNER = 'SCOTT'
  6  order by REFERENCED_OWNER, REFERENCED_TYPE, REFERENCED_NAME;

R_OWNER    REFERENCED_NAME         R_TYPE   R_LINK
---------- ----------------------- -------- -----------
BIJU       CO_OWN_VIEW             VIEW     PROD1.WORLD
BILL       ASSET_TABLE             TABLE    PROD1.WORLD
PUBLIC     BUSINESS_ASSOCIATE      SYNONYM
PUBLIC     DUAL                    SYNONYM
PUBLIC     ALLOCATION              SYNONYM
PUBLIC     ALLOCATION_TYPE         SYNONYM
PUBLIC     FIELD_DATA              SYNONYM
PUBLIC     FIELD_WELL_DATA         SYNONYM
SYS        STANDARD                PACKAGE
SYS        DUAL                    TABLE

The following query shows the objects that depend on FIELD_DATA.

SQL> select REFERENCED_OWNER, REFERENCED_TYPE, OWNER, NAME, TYPE
  2  from   DBA_DEPENDENCIES
  3  where  REFERENCED_NAME = 'FIELD_DATA';

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.