Oracle Database 11g Finer Grained Dependencies

So, here is a promised new feature for Oracle Database 11g!! Have you ever had something like this happen:

We have a view, emp_view built on EMP as seen in this query:
set lines 132
column owner format a8
column view_name format a10
column text format a50

select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ---------------------
SCOTT EMP_VIEW VALID select ename from emp

Now, we add a column to EMP and watch what happens to the view:

alter table emp add (new_column number);
select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ---------------------
SCOTT EMP_VIEW INVALID select ename from emp


Now.... Oracle database 11g has improved dependency management. Let's look at this example in 11g:

select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ---------------------
SCOTT EMP_VIEW VALID select ename from emp


alter table emp add (new_column number);
select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ------------------------------
SCOTT EMP_VIEW VALID select ename from emp


Note in 11g that the update did not invalidate the view. If the change had been to the ename column, then it would have invalidated the view since there is a direct dependency between the ename column and the view. This same new dependency logic applies to things like PL/SQL code too.

More on 11g New Feature topics in my new book, Oracle Database 11g New Features from Oracle Press.