DEPENDENCIES

Ever want to drop an old procedure, but you didn't know if it was being used by some other code, perhaps in another schema? Simply check ALL_DEPENDENCIES, right. Except I hate typing those long column names. And what if I want to traverse through the dependencies? So of course, I wrote some scripts.

Never sure what's a dependency vs a reference, so I named them the way I think it works, but they might be backwards.

SHOW_DEPENDENCIES - shows what objects my code (object) uses, and continues down the tree.
SHOW_REFERENCES - shows what objects call my code (object), and continues up the tree.

Both take two parameters: &1 = owner, &2 = object (if you don't know the owner, try the OBJ_TREE script I posted earlier). And both scripts work on the underlying sys objects, so you need to be a DBA (I do a "connect by" on sys.dependency$, since the connect by on ALL_DEPENDENCIES never finished). DEPENDENCIES stops when it finds something owned by SYS, because it just gets too messy to keep showing things (remove the "d_onam <> 'SYS'" clause near the bottom to see for yourself).

An example using some FLOWS_010500 objects (htmldb provides such nice examples):

SQL> @show_dependencies FLOWS_010500 p

.

.PROCEDURE     FLOWS_010500.P

.   PACKAGE       FLOWS_010500.WWV_FLOW_LANG

.   SYNONYM       PUBLIC.HTP

.   PACKAGE       FLOWS_010500.WWV_FLOW_FILE_MGR



PL/SQL procedure successfully completed.

here's what happens if I remove the check against SYS objects

SQL> /

.

.PROCEDURE     FLOWS_010500.P

.   PACKAGE       SYS.STANDARD

.   PACKAGE       FLOWS_010500.WWV_FLOW_LANG

.      PACKAGE       SYS.STANDARD

.   SYNONYM       PUBLIC.HTP

.      PACKAGE       SYS.HTP

.         PACKAGE       SYS.STANDARD

.   PACKAGE       FLOWS_010500.WWV_FLOW_FILE_MGR

.      PACKAGE       SYS.STANDARD

.   PACKAGE       SYS.SYS_STUB_FOR_PURITY_ANALYSIS



PL/SQL procedure successfully completed.

and here's one going the other direction

SQL> @show_references FLOWS_010500 WWV_FLOW_FILE_MGR

.

.PACKAGE       FLOWS_010500.WWV_FLOW_FILE_MGR

.   PACKAGE BODY  FLOWS_010500.WWV_FLOW_FILE_MGR       INVALID

.   PACKAGE BODY  FLOWS_010500.HTMLDB_UTIL

.   PROCEDURE     FLOWS_010500.P

.      SYNONYM       PUBLIC.P

.   SYNONYM       PUBLIC.WWV_FLOW_FILE_MGR

.

.PACKAGE BODY  FLOWS_010500.WWV_FLOW_FILE_MGR            INVALID



PL/SQL procedure successfully completed.

the REFERENCES script is also nice to find out where a table is used

SQL> @show_references FLOWS_010500 WWV_FLOW_PREFERENCES$

.

.TABLE         FLOWS_010500.WWV_FLOW_PREFERENCES$

.   TRIGGER       FLOWS_010500.WWV_BIU_FLOW_PREFERENCES

.   FUNCTION      FLOWS_010500.V

.      TRIGGER       FLOWS_010500.WWV_BIU_FLOW_COLLECTION       INVALID

.      FUNCTION      FLOWS_010500.NV

.         VIEW          FLOWS_010500.WWV_FLOW_USERS

.            SYNONYM       PUBLIC.WWV_FLOW_USERS

.         VIEW          FLOWS_010500.WWV_FLOW_GROUP_USERS

that one continues on for quite some time

These scripts work on versions 9 and 10. Haven't had the chance to test them on 11 yet, so if you do, and they don't work, let me know. Or better yet, fix them for me ;)

Follow the rest of the discussion on my blog