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.