You are here

FIne Grained Dependencies in Oracle 11g

Dependency
Oracle schema is the place where all the Database objects get stored. This make a room for the entire set of objects under the Category of Data Storage ( Tables, Views) and Data Access (Stored Procedure ,Functions ,Packages and Triggers) .These Objects make a reference across all these objects to form the application.This is known as dependency.

Conventional Dependency Model
If an Object B is being referrd from object A (ie Object A refers another Object B) , Object A is known as Dependant Object and Object B is Known as referenced Object. If the referenced Object's Structue or definition is modified ,the Status of the dependant object will be marked "Invalid' in user_objects data dictionary.

Dependencies across the objects can be viewed by [USER/ALL/DBA]_DEPENDENCIES.

Besides these Data dictionary , following addtional View can also be used to track the Indirect Dependencies .
[*]DEPTREE - To Track the Direct Dependencies
[*]IDEPTREE - To Track the Indirect Dependencies.

However ,These views are not part of Data dictionary view and this should be created using the UTLDTREE.sql
script available in ORACLE_HOME/rdbms/admin folder.

Fine Grained Dependency - New Dependency Model in 11g
As of Oracle 10g ,Dependencies across the objects can be tracked down upto object level. Fine Grained Dependency is the one of key enhancement in Oracle 11g. This has taken the object dependency concept from Object level into column level .This will assure the minimum hinderence object Validation.

The Concept
The object level Dependency has been now enhanced to element Level. This means that if the object has been changed and it does not affect the dependant object in any manner and retain the status VALID.

For Instance, SP_GET_EMPLOYEE Procedure depends on the table EMPLOYEE. Now ,the EMPLOYEE Table require structual change;It has to add a new colum STATUS to track the Employee's status.

For this Sequence in 9i, the procedure SP_GET_EMPLOYEE get invalidated because the referenced object has underngone into a structual change.

But now in 11g Release, SP_GET_EMPLOYEE would retain the status VALID as the new colum "STATUS" is no where used in the procedure.