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.

Another Example with Views.

Consider the structure of the EMPLOYEE Table from the above example

COLUMN_NAME DATA_TYPE NOT NULL
EMPNO VARCHAR2(5) Yes
EMPNAME VARCHAR2(100) Yes
HIREDATE DATE
DEPTNUM NUMBER(5)
SAL NUMBER(8,5)

A View VW_EMP has been created based on the EMPLOYEE .
Structure of the View is as follows . It refers only 2 fields from the EMPLOYEE TABLE

CREATE VIEW VW_EMP AS SELECT EMPNO ,EMPNAME FROM EMPLOYEE ;

The following query would return the status VALID .

Select d.referenced_name,o.status from user_dependencies d ,user_objects o where d.name = o.object_name and d.name ='VW_EMP'

Now , the EMPLOYEE Table undergoes with the structural change of adding the new field (STATUS) .

COLUMN_NAME DATA_TYPE NOT NULL

EMPNO VARCHAR2(5) Yes
EMPNAME VARCHAR2(100) Yes
HIREDATE DATE
DEPTNUM NUMBER(5)
SAL NUMBER(8,5)
STATUS VARCHAR2(1)

In case of 9i, after the structual change the following query would return the status 'INVALID' since Oracle 9i checks the dependency in Object level.

Select d.referenced_name,o.status from user_dependencies d ,user_objects o where d.name = o.object_name and d.name ='VW_EMP'

In case of Oracle 11g,

Even after the structual change on the Table EMPLOYEE ( adding a new column STATUS) , the above query return the status VALID if it is the case of 11g since the View not rely on the column STATUS.

Comments

its very help full post for me thanks to share this

--moderator edit: link spam removed