Managing database dependencies

Oracle objects which avail the standings and services of other objects are dependent on them. Suppose, a complex view, V, is created on two tables, T1 and T2. The object, V, is dependent on T1 and T2, while T1 and T2 are the referenced objects. Therefore, as a thumb rule of dependency, a valid database object can either be a dependent or a referenced object. The thumb rule of dependency has some exceptions for synonyms and the package body. While synonyms can always be referenced objects, the package body is always a dependent object.

Database dependency can be classified as direct or indirect. Consider three objects—P, M, and N. If P references M and M references N, then P is directly dependent on M. In the same case, P and N share indirect dependency. Schema objects can refer tables, views, sequences, procedures, functions, packages specification, triggers, and synonyms in their definitions and can behave as both dependent and referenced objects. Out of these, a sequence can appear as a referenced object only, while package body can only be a dependent object.

Displaying the direct and indirect dependencies

The dependency matrix is automatically generated and maintained by the Oracle server. 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 view. The following query displays the status of our previously created functions:

/*Check the status of the function F_GET_DOUBLE*/
SQL> SELECT STATUS 
     FROM USER_OBJECTS 
     WHERE OBJECT_NAME='F_GET_DOUBLE';

STATUS
-------
VALID

DEPTREE and IDEPTREE are two views which capture and store necessary information about the direct and indirect dependencies, respectively. The views are created by a DBA by running the script from $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 (prefix with @).

3. xecute 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 as follows, to populate the dependency details of an object:

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

PL/SQL procedure successfully completed.

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

Then, the DEPTREE and IDEPTREE views can be queried to view the dependency information.

Dependency metadata

Oracle provides the data dictionary views, namely, USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES, to view 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

As per 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. Though the object recompilation can easily solve the problem, it becomes a serious hindrance in working of the object validations. Dependent objects are used to fall prey to their own dependency matrix. They are rendered invalidated even if the change is not for them.

Oracle 11g brings in a fundamental change in dependency management, known as Fine Grained Dependency (FGD). The FGD concept modifies the dependency principle as if the alteration in the referenced object does not affect the dependent object, the dependent object would remain in VALID state. The new principle was received well amongst the community as it shifted the granularity from object level to element level. For instance, if a view is created with selected columns of a table and the table is altered to add a new column, the view shall remain in a valid state.