Manage Oracle dependencies

Oracle schema is the home for all the database objects. It shelters the entire set of objects which participate in the data storage layer (tables, views) and data access layer (package, procedures, functions, triggers). These objects can establish reference across themselves to embed one or the other application logic. This relationship between the two participating objects is known as Oracle Dependency; where one object acts as ‘Dependent object’, while the other one plays the role of ‘Referenced object’.

Dependent and Referenced Object

If an object A uses another object B within its definition, then the object A is known as Dependent object (on B). In the same case, the object B would be referred as Referenced object. Once the object A is compiled successfully, it establishes the relationship with the object B by virtue of its state. This implies that Oracle server identifies the state of an object as the relationship property between two related objects.

As stated, any object can behave as dependent or referenced object in particular situation, but this is not always true. Synonyms can always be categorized under referenced objects. Similarly, package body can always be a dependent object.

Direct and Indirect dependency

Conventional Dependency Management

Usually, if the referenced object undergoes a change, all its dependent objects are marked invalid. If the change is significant for the dependent object, it would be validated by the server in the very next call. This ‘DEPENDS ON’ matrix is maintained internally by Oracle. Oracle database implicitly toggles over the status of the objects and voluntarily takes decision to validate or invalidate them. The status of an object is determined by the value in the STATUS column of USER_OBJECTS dictionary view. It can be VALID for validated objects or INVALID for invalidated objects.

For instance, a procedure P_GET_SAL queries the EMPLOYEES table to get the salary of an employee. As per the dependency equation, P_GET_SAL is dependent on EMPLOYEES (referenced) table. If EMPLOYEES table is altered to add a new column “commission”, procedure P_GET_SAL is immediately invalidated. Hereafter, whenever P_GET_SAL would be invoked, it would throw exception of ‘Snapshot too old’. But in the very next call, Oracle server compiles the invalidated objects.

Apart from the above scenario, object dependency might bump the database business layer activities for a while. In addition, if the database makes use of synonyms and view, it always runs the threat of being affected by object dependencies.

Oracle 10g even made certain modifications in synonyms to succumb over the dependency issue. Oracle 11g implemented an algorithm, known as Fine Grained Dependency, to handle the dependency from the depth. The new concept has changed the granularity of dependency from object to element. Let us now go through the concept in detail.

{mospagebreak}

Recapitulate Object status in a schema

Now, let us take a look at the possible status(s) of an object in a schema. The object status gets updated in the STATUS column of [ALL | DBA | USER]_OBJECTS dictionary view for all objects within the database/schema. The column can also be found in [ALL | DBA | USER]_PROCEDURES which holds subprogram and package information only. I will list the probable status(s) of an existing object.

USER_DEPENDENCIES view

The dependency matrix of an object is maintained by [ALL | DBA | USER]_DEPENDENCIES dictionary view in Oracle. It captures the referenced object’s name, type, owner, and db link information.

Track Direct and Indirect dependency views

Besides USER_DEPENDENCIES dictionary view, Oracle provides two additional views to track direct and indirect dependency of the object. These views are DEPTREE (direct dependencies) and IDEPTREE (indirect dependencies), which do not exist in the schema as other dictionary view, but their script can be found in ORACLE_HOME.

Steps to follow

1. Execute the UTLDTREE.sql script from ORACLE_HOME/rdbms/admin folder.
2. Execute the DEPTREE_FILL procedure to populate the DEPTREE_TEMPTAB table.

How to use?

Suppose, I want to check the dependency level of a procedure P_GET_SAL, I would execute the DEPTREE_FILL procedure in the below fashion.

EXEC DEPTREE_FILL(‘PROCEDURE’,’SCOTT’,’P_GET_SAL’);

Above statement would populate the dependency matrix into DEPTREE_TEMPTAB table. The view DEPTREE AND IDEPTREE are created on top of DEPTREE_TEMPTAB.

{mospagebreak}

Fine grained dependency

Fine grained dependency is one of the key enhancements of Oracle 11g. The new concept has taken the object dependency concept to an instrumental level so as to assure minimum hindrance in database object validations.

The Concept

The object level dependency has been made more precise to element level. This implies that if the change done to an object does not affect its dependent objects in any manner, they would retain their VALID state.

For example, I have a procedure P_GET_SAL which references EMPLOYEES table to get the salary of an employee. EMPLOYEES table is required to undergo a structural change; it has to be altered to add a new column STATUS.
For the same sequence of actions in Oracle 9i, the procedure P_GET_SAL would have been set as INVALID. This is because the object snapshot is proportional to the status of its referenced objects. If any of the referenced objects attains ‘invalid’ state, the ‘using’ object would also be invalidated.

But now with 11g release, P_GET_SAL would have remained in VALID state even after the EMPLOYEES table is altered. This is because the newly added column is no where used in the package.

Illustrations: Example would better demonstrate the concept.

In Views

Views are one of the most referred examples on object dependency. Let us see how the Oracle dependency has changed its behavior in 11g release, in comparison to its prior releases.
A view VU_EMP has been created on EMP table as show below. Note the query definition of the view as it is build on two columns only.

Now, the table EMP has been altered to include the STATUS column.

Fetch the STATUS of the view from USER_DEPENDENCIES. It remains in VALID state and thus the entire dependent objects of the view remain valid.

The same above query when executed in 9i returned INVALID state. The reason for such behavior is that Oracle 9i checks the dependency at object level.

{mospagebreak}

In Packages

Many a time, Oracle packages too get affected by Object level Oracle dependency. I would illustrate a simple example.

A package PKG_FGD has been created for demonstration purpose. Currently, it has only one subprogram GET_SAL.

Display the content of the EMP nested table through a stored procedure P_PRINT_SAL

Check the status of the P_PRINT_SAL

Now, I would add one more subprogram GET_JOB to the package as below.

Without recompiling the stand alone procedure P_PRINT_SAL, check its status in the USER_DEPENDENCIES table.

The same scenario in Oracle 10g (or earlier version) would have yielded INVALID status for the procedure P_PRINT_SAL. It could be recompiled using ALTER PROCEDURE [NAME] RECOMPILE statement. Even Oracle database used to perform auto recompilation of invalidated program units in second invocation, not in the first reference.

Besides the above citations, synonym redefinition is another fair example to demonstrate the change in Oracle dependency. If synonym is redefined using columns with same name and type, the dependent objects would not be invalidated.

Conclusion

Fine Grained Dependency tracking has reformed the object dependency policy of databases. The chance of dependent object invalidation has been reduced to the impact of changes in the referenced object. Even redefinitions and restructuring of views, synonyms, tables or packages would not hamper the working of their dependent object.

[catlist id=185].