当一个存储过程所依赖(引用的)对象发生某些更改时,会使得存储过程失效(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

Trackback

only 1 comment untill now

  1. 学习了~~~Thanks

Add your comment now