Dependencies in oracle

How to Track and manage dependencies?

Some database objects refer other objects as part of their definition.e.g View created by table references or other views.Similarly stored subprograms can include SQL statements that refer tables or other sub programs.

How to query dependent objects on particular object type?

Managing dependencies

When definition of object A includes reference to object B then A is dependent of object B and B is referenced object of A.
That means defintion of referenced object is changes then functioning of dependent object is get prevented.
e.g When table is dropped then any view that queries table will get no longer in function.

Direct dependencies: A is direct dependent of B
B is direct dependent of C

Indirect dependencies: A is indirect dependent of C.

Cascading invalidation is the one when changes to C invalidates B, also invalidates A.

Invalidation of objects can be course-grained or fine grained:

Course grained invalidation – Any DDL statement that changes referenced object invalidates all its dependencies.

Fine grained invalidation – A DDL statement that changes a referenced object invalidates only dependents for which dependent relies on attribute of that DDL changed.

If an object is not valid when it is referenced,it must be validated before it getting referenced.
When local subprogram is compiled with dependency on remote subprogram, The compilation timestamp of remote subprogram is stored in object code of local subprogram.
Any time that local subprogram is executed,the recorded timestamp of remote subprogram will be compared with current timestamp. If the current timestamp is later than the date stored in local subprograms object code then error will returned.
The local subprogram will be marked as invalid.If it is executed second time, subprogram will successfully recompile and new timestamp of remote subprogram will be stored in local subprograms object code.

To avoid this invalidation of dependents
1.Add new items to the end of package.This will prevent entry point invalidation of top level package items.
2.Refer tables indirectly using views..This will enable to add columns to the table without invalidating dependent views or any dependent PLSQL object.
Even we can also delete columns not referred by that view without invalidating dependent object. Dependencies can be viewd directly by right clicking on required object check.

dependencies in oracle

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j