Managing database dependencies

PL/SQL program units, as well as other database objects such as views, may refer to other database objects in their procedural section. The calling program unit is said to be dependent on the called program units (known as referenced objects). If EMP and DEPT are the base tables used in creating a view V_EMP_REP, then the view is dependent on EMP and DEPT.

Note

A sequence can always be a referenced object. A package body is always a dependent object.

Database dependency can be classified as direct or indirect. Consider three objects—P, M, and N. If object P references object M and object M references object N, then P is directly dependent on M and indirectly dependent on N.

Displaying the direct and indirect dependencies

The dependency matrix is automatically generated and maintained within the Oracle Database. The status of an object is the basis of dependency among the objects. The status of an object can be queried from the USER_OBJECTS (or ALL_OBJECTS or DBA_OBJECTS) dictionary view. The following query queries the status of the function F_GET_DOUBLE:

/*Check the status of the function F_GET_DOUBLE*/
SELECT status
FROM user_objects
WHERE object_name='F_GET_DOUBLE'
/

STATUS
-------
VALID

The system views DEPTREE and IDEPTREE capture the necessary information about the direct and indirect dependencies. Database administrators can create the views by running the script $ORACLE_HOME\RDBMS\ADMIN\utldtree.sql.

The execution steps for the script are as follows:

1. Login as SYSDBA in SQL Developer or SQL*Plus.
2. Copy the complete path and script name (prefixed with @).
3. Execute the script (with F9).
4. Query the DEPTREE and IDEPTREE views to verify their creation.

The script creates the DEPTREE_TEMPTAB table and the DEPTREE_FILL procedure. The DEPTREE_FILL procedure can be executed to populate the dependency details of an object.

/*Populate the dependency matrix for the function F_GET_DOUBLE*/
SQL> EXEC DEPTREE_FILL('FUNCTION','SCOTT','F_GET_DOUBLE');

PL/SQL procedure successfully completed.

Note that the first parameter of the DEPTREE_FILL procedure is the object type, the second is the owner, and the third is the object name.

The DEPTREE and IDEPTREE views can now be queried to view the dependency information.

Dependency metadata

Oracle provides the data dictionary views (USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES) to view the complete dependency metrics shared by an object. Besides the dependent object's list, it also lists its referencing object name and owner.

The following screenshot shows the structure of the dictionary view DBA_DEPENDENCIES:

Dependency issues and enhancements

In line with the conventional dependency phenomenon, the status validity of the dependent object depends upon the status of the referenced object. So, if the definition of the referenced object is altered, the dependent object is marked INVALID in the USER_OBJECTS view. Although object recompilation can easily solve the problem, the object invalidations may impact the application flow.

Oracle 11g introduced Fine Grained Dependency Tracking (FGD) to modify the dependency principle as follows. If the alteration in the referenced object does not affect the dependent object, the dependent object will remain in the VALID state. For instance, if a view is created with a fixed set of columns of a table and the table is altered to add a new column, the view will remain in a VALID state.