Fine grained Dependency

With every new release, Oracle is achieving more and more high availability standards not only through newer and enhanced technologies but also through the improvement in existing behavior like fine-grained dependency introduced in oracle 11g.

In previous releases, whenever underlying table’s definition gets altered either for adding new column(s), dropping column(s) or changing column attribute(s), all the dependent objects becomes invalid whether the column in question is being used or not in any of the view, procedure, function and/or package but with 11g, only those dependent objects will be invalidated which uses the column in question. Let us start straight with an example. Create following table and view in both 10g and 11g databases.

In 11g, since new column is not part of the view, it remains ‘VALID’ while in 10g it has become invalid. Similar behavior is expected for procedures and functions as well. Let us create following procedure and function both in 10g and 11g databases.

Now let us alter the column length of the column test_id or drop it altogether and check the status of the procedure and function again. For 10g both procedure and function will be invalidated even though function is not using column being modified. In Oracle 11g, only procedure will be invalidated.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on March 17, 2009 at 11:00 am and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.