当一个存储过程所依赖（引用的）对象发生某些更改时，会使得存储过程失效（invalidated），比如存储过程依赖（引用）的表增加减少了列、存储过程依赖（引用）的存储过程被重新编译。本文将介绍一种特殊的会引起存储过程失效的情况。
下面通过测试来演示与DB LINK相关的存储过程失效的情况：
1. 在TEST用户下创建到db1的DB LINK：
SQL> create database link to_db connect to perfstat identified by xxx using 'db1'; 数据库链接已创建。
2.在TEST2用户下创建到db2的DB LINK，DB LINK的名称仍然为to_db，但实际上连接的数据库与TEST用户下to_db这个DB LINK连接的数据库并不是同一个数据库：
SQL> create database link to_db connect to perfstat identified by xxx using 'db2'; 数据库链接已创建。
3. 在TEST用户下创建存储过程TEST_P1：
SQL> create or replace procedure test_p1 2 is 3 v_dbid number; 4 begin 5 select dbid into v_dbid from stats$snapshot@to_db where rownum<2; 6 end; 7 / 过程已创建。 SQL> select object_id,status from user_objects where object_name='TEST_P1'; OBJECT_ID STATUS ---------- ---------- 18443 VALID
4. 在TEST2用户下创建存储过程TEST2_P1，这里存储过程TEST2_P1的代码与TEST用户下存储过程TEST_P1的代码明显不同。这两个存储过程的共同点是都引用了STATS$SNAPSHOT@TO_DB这个远程表。由于TEST和TEST2用户下TO_DB连接的是不同的数据库，因此这2个存储过程引用的STATS$SNAPSHOT@TO_DB位于不同的数据库上，也就是说是不同的表：
SQL> create or replace procedure test2_p1 2 is 3 v_snap_time date; 4 begin 5 select snap_time into v_snap_time from stats$snapshot@to_db where rownum<2; 6 end; 7 / 过程已创建。 SQL> select object_id,status from user_objects where object_name='TEST2_P1'; OBJECT_ID STATUS ---------- ---------- 18445 VALID
5. 在TEST用户下查看存储过程TEST_P1的状态，发现其状态为INVALID，而实际上这个时候这个存储过程以及其引用的对象没有任何变更：
SQL> select object_id,status from user_objects where object_name='TEST_P1'; OBJECT_ID STATUS ---------- ---------- 18443 INVALID
6. 如果重新编译TEST.TEST_P1，那么其状态会成为VALID，但是这个时候TEST2.TEST2_P1则又莫名其妙地变成为INVALID：
SQL> alter procedure test.test_p1 compile;
过程已更改。
SQL> select object_id,owner,object_name,status from dba_objects
where object_name in ('TEST_P1','TEST2_P1');
OBJECT_ID OWNER OBJECT_NAME STATUS
---------- --------------- ------------------------------ ----------
18443 TEST TEST_P1 VALID
18445 TEST2 TEST2_P1 INVALID
7. 在TEST2用户下执行存储过程TEST2_P1，然后再次检查存储过程状态：
SQL> exec test2_p1
PL/SQL 过程已成功完成。
SQL> select object_id,owner,object_name,status from dba_objects
2 where object_name in ('TEST_P1','TEST2_P1');
OBJECT_ID OWNER OBJECT_NAME STATUS
---------- --------------- ------------------------------ ----------
18443 TEST TEST_P1 INVALID
18445 TEST2 TEST2_P1 VALID
可以看到，TEST.TEST_P1失效了，而TEST2.TEST2_P1又正常了。
在这里就可以提出问题了：为什么创建了TEST2.TEST2_P1这个存储过程之后，TEST.TEST_P1就失效了？为什么TEST.TEST_P1重新编译之后TEST2.TEST2_P1又失效了？为什么TEST2.TEST2_P1重新执行（有一个隐式的编译过程）后，TEST.TEST_P1又失效了？
其实上以上三个问题可以归纳为一个问题：为什么TEST.TEST_P1和TEST2.TEST2_P1这2个存储过程，在其中一个状态正常的情况下，另一个为失效状态？ Read the rest of this entry