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';
 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. |