当一个存储过程所依赖（引用的）对象发生某些更改时，会使得存储过程失效（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个存储过程，在其中一个状态正常的情况下，另一个为失效状态？
此时TEST.TEST_P1这个存储过程是失效状态，那么我们看看这个存储过程引用的哪个对象存在问题：
SQL> select po.obj# p_obj, 2 po.name p_name, 3 to_char(p_timestamp, 'DD-MON-YYYY HH24:MI:SS') "P_Timestamp", 4 to_char(po.stime, 'DD-MON-YYYY HH24:MI:SS') "STIME", 5 decode(sign(po.stime - p_timestamp), 0, 'SAME', '*DIFFER*') X, 6 po.status 7 from sys.obj$ do, sys.dependency$ d, sys.obj$ po 8 where P_OBJ# = po.obj#(+) 9 and D_OBJ# = do.obj# 10 and do.obj# = 18443 11 order by 2, 1; P_OBJ P_NAME P_Timestamp STIME X STATUS ---------- ------------------------------ ------------------------- ------------------------- -------- ------ 648 STANDARD 18-4月 -2003 00:00:00 18-4月 -2003 00:00:00 SAME 1 18444 STATS$SNAPSHOT 25-1月 -2011 18:20:01 26-1月 -2011 11:21:27 *DIFFER* 1 3217 SYS_STUB_FOR_PURITY_ANALYSIS 15-3月 -2009 17:43:22 15-3月 -2009 17:43:22 SAME 1 SQL> select owner#,name,type#,linkname,remoteowner 2 from obj$ 3 where obj#=18444; OWNER# NAME TYPE# LINKNAME REMOTEOWNER ---------- --------------- ---------- -------------------- ------------ 0 STATS$SNAPSHOT 2 TO_DB PERFSTAT
在上面的输出中，P_TIMESTAMP是指对象产生依赖关系时所依赖的对象的specification time。STIME指的是依赖对象现在的specification time，二者不一者，说明所依赖对象的specification发生过更改，引起对象的引效。
这里不一致的依赖对象正是PERFSTAT.STATS$SNAPSHOT@TO_DB。我们再看看存储过程TEST2.TEST2_P1的依赖对象：
SQL> select po.obj# p_obj, 2 po.name p_name, 3 to_char(p_timestamp, 'DD-MON-YYYY HH24:MI:SS') "P_Timestamp", 4 to_char(po.stime, 'DD-MON-YYYY HH24:MI:SS') "STIME", 5 decode(sign(po.stime - p_timestamp), 0, 'SAME', '*DIFFER*') X, 6 po.status 7 from sys.obj$ do, sys.dependency$ d, sys.obj$ po 8 where P_OBJ# = po.obj#(+) 9 and D_OBJ# = do.obj# 10 and do.obj# = 18445 11 order by 2, 1; P_OBJ P_NAME P_Timestamp STIME X STATUS ---------- ------------------------------ ------------------------- ------------------------- -------- ------ 648 STANDARD 18-4月 -2003 00:00:00 18-4月 -2003 00:00:00 SAME 1 18444 STATS$SNAPSHOT 26-1月 -2011 11:21:27 26-1月 -2011 11:21:27 SAME 1 3217 SYS_STUB_FOR_PURITY_ANALYSIS 15-3月 -2009 17:43:22 15-3月 -2009 17:43:22 SAME 1
可以看到TEST2.TEST2_P1这个存储过程同样依赖（引用）object id为18444的对象，也就是PERFSTAT.STATS$SNAPSHOT@TO_DB。也就是说，TEST.TEST_P1和TEST2.TEST2_P2这两个存储过程都依赖于这同一个对象。然而，不幸的是，实际上这两个存储过程所引用的PERFSTAT.STATS$SNAPSHOT@TO_DB对象实际上是不同远程数据库上的对象。这一依赖（引用）对象的唯一标识为REMOTEOWNER, LINKNAME以及NAME，而实际上这三个关键字组合起来并不一定是唯一的。
从面上的数据可以得到，在TEST用户下TO_DB远程库中，PERFSTAT.STATS$SNAPSHOT的specification time为"25-1月 -2011 18:20:01"，而TEST2用户下TO_DB远程库中，PERFSTAT.STATS$SNAPSHOT的specification time为"26-1月 -2011 11:21:27"。当TEST.TEST_P1重新编译时，object id为18444这个对象的specification time（即obj$中的stime列）为"25-1月 -2011 18:20:01"，这样就使TEST2.TEST2_P1的对应依赖对象的时间戳不一致而使其失效。而TEST2.TEST2_P1重新编译后，object id为18444这个对象的specification time（即obj$中的stime列）为"26-1月 -2011 11:21:27"，基于同样的理由，TEST.TEST_P1也失效。
这一问题看上去是Oracle在处理DB LINK远程对象的依赖关系存在缺陷所致。这一问题的触发有如下的条件：不同用户下相同名称的DB LINK连接到不同的数据库，同时这些不同的用户下的存储过程引用了这些DB LINK远程库中的相同用户名下的相同名称的对象。为避免这一问题，不管目前有没有这样的问题，有一个原则：在创建 DB LINK时，连接到不同数据库的DB LINK，其名称不能相同。
实际上在生产环境中，已经数次遇到这样的问题，存储过程和PACKAGE经常失效。在这样的生产环境中，由于数据量和业务量巨大，通常将一套业务系统的数据库按某个规则（比如地域）拆分为多套库，很显然其结构是相同的；而另一套系统的数据库需要通过DB LINK访问已经拆分的多套数据库，而在不同的用户下相同名称的DB LINK指向了不同的数据库，由于本文提及的问题，引起了存储过程和PACKAGE经常失效。
update:
实际上，在MOS文档中也有提及这个问题：
Objects sharing same remote object names(on different databases) and same db link name invalidate each other [ID 270534.1]
Bug 2485372: OBJECTS SHARED SAME REMOTE OBJECT NAME INVALIDATES EACH OTHER
这个BUG从Oracle8i就有了。这个问题至今都没有修复。其解决办法仍然是“同一个数据库中指向不同数据库的DB LINK不能取相同的名字”。
--2011-09-16
学习了~~~Thanks
[回复]