最近看Oracle的文档,在模式对象依赖关系一节
看到Recompilation occurs automatically when the invalid dependent object is referenced.这样一个表述.
引发了我对在此情况下对象状态一探究竟的想法.

我在Oracle11gR2环境下进行实验.

SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE    11.2.0.1.0    Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production

为了进行实验我先要创建一个表test(此处我选择基于已有的表来创建,是为了我自己方便)和视图view_1和view_2.视图view_1依赖于test,view_2依赖于view_1

SQL> create table test as select * from ss; Table created SQL> create view view_1 as select * from test; View created SQL> create view view_2 as select * from view_1; View created

此时表和视图的状态都是VALID（有效）

SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                      STATUS ------------------------------ -------------------------------------------------------------------------- ------- APP                                TEST                                                                                      　VALID APP                                VIEW_1                                                                                 　VALID APP                                VIEW_2                                                                                 　VALID APP                                SS                                                                                         　 VALID

当删除test时，依赖于此表的view_1状态变为INVALID，相应的，依赖view_1的view_2的状态也变为INVALID

SQL> drop table test; Table dropped SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                STATUS ------------------------------ ---------------------------------------------------------------------- ------- APP                                VIEW_1                                                                           INVALID APP                                VIEW_2                                                                           INVALID APP                                SS                                                                                    VALID

我们重建了被引用对象test后，view_1和view_2的状态仍为INVALID，没有改变。

SQL> create table test as select * from ss; Table created SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                      STATUS ------------------------------ -------------------------------------------------------------------------- ------- APP                                VIEW_1                                                                                 INVALID APP                                VIEW_2                                                                                 INVALID APP                                TEST                                                                                      VALID APP                                SS                                                                                          VALID

此时，我们对view_1进行查询。被引用后view_1的状态变为VALID，但view_2仍为INVALID

SQL> select count(*) from view_1;   COUNT(*) ----------          4 SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                      STATUS ------------------------------ -------------------------------------------------------------------------- ------- APP                                VIEW_1                                                                                 VALID APP                                VIEW_2                                                                                 INVALID APP                                TEST                                                                                      VALID APP                                SS                                                                                          VALID

再次删除test对象后，view_1和view_2的状态全为INVALID

SQL> drop table test; Table dropped SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                STATUS ------------------------------ ---------------------------------------------------------------------- ------- APP                                VIEW_1                                                                           INVALID APP                                VIEW_2                                                                           INVALID APP                                SS                                                                                    VALID

重建对象test(从上面的操作可知,此时view_1和view_2全为INVALID)，再对view_2进行查询。view_2被引用后，再查询状态，发现view_1和view_2全为VALID。也就是说，对view_2进行引用时，也完成了对view_1引用。

SQL> create table test as select * from ss; Table created SQL> select count(*) from view_2;   COUNT(*) ----------          4 SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                      STATUS ------------------------------ -------------------------------------------------------------------------- ------- APP                                TEST                                                                                      VALID APP                                VIEW_1                                                                                 VALID APP                                VIEW_2                                                                                 VALID APP                                SS                                                                                          VALID

按书中所说,实效对象在引用时发生的事，和重新编译此对象的效果应该是一样的，我先把test 对象删掉，再重建对象test。按之前的结论，此时view_1和view_2的状态全为INVALID

SQL> drop table test; Table dropped SQL> create table test as select * from ss; Table created

最后，对view_2进行编译，之后，view_1和view_2的状态就全变为VALID了

SQL> alter view_2 compile; alter view_2 compile SQL> select owner,object_name,status from dba_objects where wner='APP'; OWNER                         OBJECT_NAME                                                                      STATUS ------------------------------ -------------------------------------------------------------------------- ------- APP                                TEST                                                                                      VALID APP                                VIEW_1                                                                                 VALID APP                                VIEW_2                                                                                 VALID APP                                SS                                                                                          VALID

可见，当一个对象的依赖对象被删除时，其状态会自动转为INVALID；而除非此对象被引用（系统自动编译）或手动编译（当然，得编译通过），否则其状态不会变为VALID。