Oracle11g對依賴的判斷達到字段級
在Oracle 10g下，判斷依賴性只達到了對象級，也就是說存儲過程訪問的對象一旦發生了變化，那麼Oracle就會將存儲過程置為INVALID狀態。所以在為表做了DDL操作後，需要把存儲過程重新進行編譯。
在Oracle 11g下，對依賴的判斷更加細化，判斷到了字段級。雖然有這麼好的特性，但我還是建議做了DDL後把存儲過程再重新編譯一下。
Oracle 10g下：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> drop table TEST purge;
SQL> drop PROCEDURE P_TEST;
SQL> CREATE TABLE TEST(ID NUMBER);
SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
INSERT INTO TEST (ID) VALUES (100);
END;
/
SQL> col OBJECT_NAME format a10;
SQL> col OBJECT_TYPE format a10;
SQL> col STATUS format a10;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P_TEST';
OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- ----------
P_TEST PROCEDURE VALID
SQL> ALTER TABLE TEST ADD (NAME VARCHAR2(30));
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P_TEST';
OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- ----------
P_TEST PROCEDURE INVALID
Oracle 11g下：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> drop table TEST purge;
SQL> drop PROCEDURE P_TEST;
SQL> CREATE TABLE TEST(ID NUMBER);
SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
INSERT INTO TEST (ID) VALUES (100);
END;
/
SQL> col OBJECT_NAME format a10;
SQL> col OBJECT_TYPE format a10;
SQL> col STATUS format a10;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P_TEST';
OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- ----------
P_TEST PROCEDURE VALID
SQL> ALTER TABLE TEST ADD (NAME VARCHAR2(30));
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P_TEST';
OBJECT_NAM OBJECT_TYP STATUS
---------- ---------- ----------
P_TEST PROCEDURE VALID
