Minimum Dependent Object Recompilation After Online Table Redefinition

Starting from Oracle 11g, If you add a column to a table, then no need to recompile its dependent objects.

Let us see what happens to a view when we add a column to a table in Oracle 9i:

 C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Dec 21 04:15:41 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table t (n number,v varchar2(10));

Table created.

SQL> insert into t values (1,'H');

1 row created.

SQL> create view tv as select * from t;

View created.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SQL> alter table t add (m number);

Table altered.

SQL> select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       INVALID

SQL>

Now let us see what happens to a view when we add a column to a table in Oracle 11g:

C:\Documents and Settings\hnaik>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 21 15:54:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@orcl11g>create table t (n number,v varchar2(10));

Table created.

SCOTT@orcl11g>insert into t values (1,'H');

1 row created.

SCOTT@orcl11g>create view tv as select * from t;

View created.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>alter table t add (m number);

Table altered.

SCOTT@orcl11g>select object_name,object_type,status from user_objects where object_name='TV';

OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- -------
TV         VIEW       VALID

SCOTT@orcl11g>

Reference:
Oracle® Database New Features Guide 11g Release 1 (11.1)