Listing Dependency Management Information

• USER_DEPENDENCIES, ALL_DEPENDENCIES, DBA_DEPENDENCIES

• USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS

Consider the following statements for Examples 1 and 2:

CREATE TABLE emp . . .;

CREATE PROCEDURE hire_emp BEGIN . . . END;

ALTER TABLE emp . . . ;

Example 1 Listing the Status of an Object

The ALL_OBJECTS data dictionary view lists information about all the objects available to the current user and the current status (that is, valid or invalid) of each object. For example, the following query lists the names, types, and current status of all objects available to the current user:

SELECT object_name, object_type, status
   FROM all_objects;

The following results might be returned:

OBJECT_NAME     OBJECT_TYPE     STATUS
--------------- --------------- ------------
EMP             TABLE           VALID
HIRE_EMP        PROCEDURE       INVALID

Example 2 Listing Dependencies

The DBA_DEPENDENCIES data dictionary view lists all dependent objects in the database and the objects on which they directly depend. For example, the following query lists all the dependent objects in JWARD's schema:

SELECT name, type, referenced_name, referenced_type
   FROM sys.dba_dependencies
   WHERE owner = 'JWARD';

If JWARD issued the example statements at the beginning of this section, the following results might be returned:

NAME        TYPE         REFERENCED_NAME     REFERENCED_TYPE
----------- ------------ ------------------- ---------------
HIRE_EMP    PROCEDURE    EMP                 TABLE

The Dependency Tracking Utility

To create the dependency tracking utility, you must run the SQL script UTLDTREE.SQL. The location of this file is operating system dependent. The UTLDTREE.SQL script creates the following schema objects:

Table DEPTREE_TEMPTAB

Structure: object_id NUMBER referenced_object_id NUMBER nest_level NUMBER seq# NUMBER

A temporary table used to store dependency information returned by the DEPTREE_FILL procedure.

View DEPTREE

Column names: nested_level, object_type, owner, object_name, seq#

A view that lists dependency information in the DEPTREE_TEMPTAB table. The parent object is listed with a NESTED_LEVEL of 0, and dependent objects are listed with a nested level greater than 0.

View IDEPTREE

Column name: dependencies

A view that lists dependency information in the DEPTREE_TEMPTAB table. Output is in a graphical format, with dependent objects indented from the objects on which they depend.

Sequence DEPTREE_SEQ

A sequence used to uniquely identify sets of dependency information stored in the DEPTREE_TEMPTAB.

Procedure DEPTREE_FILL

Syntax: DEPTREE_FILL(object_type CHAR, object_owner CHAR, object_name CHAR)

A procedure that first clears the DEPTREE_TEMPTAB table in the executor's schema, then fills the same table to indicate the objects that directly or indirectly depend on (that is, reference) the specified object. All objects that recursively reference the specified object are listed, assuming the user has permission to know of their existence.

Using UTLDTREE While Connected as INTERNAL

Example

These SQL statements show how the UTLDTREE utility can be used to track dependencies of an object. Assume the following SQL statements:

CONNECT scott/tiger;

CREATE TABLE scott.emp ( .... );

CREATE SEQUENCE scott.emp_sequence;

CREATE VIEW scott.sales_employees AS
   SELECT * FROM scott.emp WHERE deptno = 10;

CREATE PROCEDURE scott.hire_salesperson (name VARCHAR2,
   job VARCHAR2, mgr NUMBER, hiredate DATE, sal NUMBER,
   comm NUMBER)
IS
BEGIN
   INSERT INTO scott.sales_employees
      VALUES (scott.emp_sequence.NEXTVAL, name, job, mgr,
         hiredate, sal, comm, 10;
END;

CREATE PROCEDURE scott.fire_salesperson (emp_id NUMBER) IS
BEGIN
   DELETE FROM scott.sales_employees WHERE empno = emp_id;
END;

SELECT * FROM scott.emp;

SELECT * FROM scott.sales_employees;

EXECUTE scott.hire_salesperson ('ARNALL', 'MANAGER', 7839, /
                                 SYSDATE, 1000, 500);

EXECUTE scott.fire_salesperson (7934);

Assume that before SCOTT alters the EMP table, he would like to know all the dependent objects that will be invalidated as a result of altering the EMP table. The following procedure execution fills the DEPTREE_TEMPTAB table with dependency information regarding the EMP table (executed using Server Manager):

EXECUTE deptree_fill('TABLE', 'SCOTT', 'EMP');

The following two queries show the previously collected dependency information for the EMP table:

SELECT * FROM deptree;

NESTED_LEV TYPE       OWNER          NAME               SEQ#
---------- ---------- -------------- ------------------ ----
         0 TABLE      SCOTT          EMP                   0
         1 VIEW       SCOTT          SALES_EMPLOYEES       1
         2 PROCEDURE  SCOTT          FIRE_SALESPERSON      2
         2 PROCEDURE  SCOTT          HIRE_SALESPERSON      3

SELECT * FROM ideptree;

DEPENDENCIES
----------------------------------------
TABLE SCOTT.EMP
VIEW SCOTT.SALES_EMPLOYEES
PROCEDURE SCOTT.FIRE_SALESPERSON
PROCEDURE SCOTT.HIRE_SALESPERSON

Alternatively, you can reveal all of the cursors that depend on the EMP table (dependent shared SQL areas currently in the shared pool) using the UTLDTREE utility. After connecting as INTERNAL and collecting dependency information for the table SCOTT.EMP, issue the following two queries:

SELECT * FROM deptree;

NESTED_LEV TYPE   OWNER    NAME                         SEQ#
---------- ------ -------- ---------------------------- ----
         0 TABLE  SCOTT    EMP                             0
         1 CURSOR <shared> "select * from scott.emp      0.5
         2 CURSOR <shared> "select * from scott.sa. . .  7.5
         3 CURSOR <shared> "BEGIN hire_salesperson. . .  9.5
         3 CURSOR <shared> "BEGIN fire_salesperson. . .  8.5

SELECT * FROM ideptree;

DEPENDENCIES
------------------------------------------------------------
TABLE STEVE.EMP
   CURSOR <shared>."select * from scott.emp"
   CURSOR <shared>."select * from scott.sales_employee"
   CURSOR <shared>."BEGIN  hire_salesperson ('ARN. . .
   CURSOR <shared>."BEGIN  fire_salesperson (7934) END"