10g包含同名觸發器的表執行PURGE TABLE出錯

[20150908]10g包含同名觸發器的表執行PURGE TABLE出錯.txt

--昨天看楊大師的帖子http://blog.itpub.net/4227/viewspace-68964/，提到在10g同名觸發器的表執行PURGE TABLE出錯。自己也重複測試看看：

1.建立測試環境:
SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE TABLE T (ID NUMBER);

CREATE OR REPLACE TRIGGER T BEFORE INSERT ON T
FOR EACH ROW
BEGIN
NULL;
END;
/

DROP TABLE T;

SCOTT@test> PURGE TABLE T;
Table purged.

--11g沒有這個問題。10g下會報錯。

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> PURGE TABLE T;
PURGE TABLE T
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

SCOTT@test> column OBJECT_NAME format a30
SCOTT@test> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
OBJECT_NAME                    ORIGINAL_NAME  TYPE
------------------------------ -------------- -------
BIN$HzmDr6mW3qHgUKjAWWQSjg==$0 T              TRIGGER
BIN$HzmDr6mX3qHgUKjAWWQSjg==$0 T              TABLE

2.10046 跟蹤看看(10g):

WAIT #8: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1407912893606203
WAIT #8: nam='SQL*Net message from client' ela= 1450874 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1407912895057583
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #9 len=13 dep=0 uid=57 oct=200 lid=57 tim=1407912895057874 hv=928120984 ad='7bd9d828'
PURGE TABLE T
END OF STMT
PARSE #9:c=0,e=217,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1407912895057870
BINDS #9:
=====================
PARSING IN CURSOR #1 len=206 dep=1 uid=0 oct=3 lid=0 tim=1407912895058015 hv=669666613 ad='6f888760'
select o.obj#, o.name, rb.original_name    from obj$ o, RecycleBin$ rb    where o.obj#=rb.obj# AND rb.owner#=:1 AND      (rb.original_name=:2 OR (o.owner#=rb.owner# AND o.name=:3 ))      order by rb.dropscn
END OF STMT
PARSE #1:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1407912895058012
BINDS #1:
kkscoacd
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=88 off=0
  kxsbbbfp=7fa1867e5fe0  bln=22  avl=02  flg=05
  value=57
Bind#1
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
  kxsbbbfp=7fa1867e5ff8  bln=32  avl=01  flg=01
  value="T"
Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=56
  kxsbbbfp=7fa1867e6018  bln=32  avl=01  flg=01
  value="T"
EXEC #1:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1407912895058277
FETCH #1:c=0,e=68,p=0,cr=9,cu=0,mis=0,r=1,dep=1,og=4,tim=1407912895058389
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=9 pr=0 pw=0 time=69 us)'
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=9 pr=0 pw=0 time=53 us)'
STAT #1 id=3 cnt=2 pid=2 pos=1 obj=703 op='TABLE ACCESS FULL RECYCLEBIN$ (cr=3 pr=0 pw=0 time=27 us)'
STAT #1 id=4 cnt=2 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=6 pr=0 pw=0 time=23 us)'
STAT #1 id=5 cnt=2 pid=4 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=4 pr=0 pw=0 time=15 us)'
EXEC #9:c=1000,e=638,p=0,cr=9,cu=0,mis=0,r=0,dep=0,og=1,tim=1407912895058573
ERROR #9:err=38307 tim=3474725600
WAIT #9: nam='SQL*Net break/reset to client' ela= 1 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1407912895058644
WAIT #9: nam='SQL*Net break/reset to client' ela= 47 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1407912895058711
WAIT #9: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1407912895058737
WAIT #9: nam='SQL*Net message from client' ela= 4143603 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1407912899202366
=====================

--帶入引數執行，注意我增加1列o.DATAOBJ#。
SELECT o.obj#, o.DATAOBJ#,o.name, rb.original_name
    FROM sys.obj$ o, sys.RecycleBin$ rb
   WHERE     o.obj# = rb.obj#
         AND rb.owner# = 57
         AND (rb.original_name = 'T' OR (o.owner# = rb.owner# AND o.name = 'T'))
ORDER BY rb.dropscn;

        OBJ#     DATAOBJ# NAME                           ORIGINAL_NAME
------------ ------------ ------------------------------ --------------------------------
       67542              BIN$HzmVenGmJJzgUKjAWWQSzA==$0 T
       67541        67541 BIN$HzmVenGnJJzgUKjAWWQSzA==$0 T

--第1條記錄DATAOBJ#為NULL，沒有資料段。應該是觸發器。

SCOTT@test> select obj#,dataobj#,type# from sys.obj$ where  obj#=67542 or  obj#=67541;
        OBJ#     DATAOBJ#        TYPE#
------------ ------------ ------------
       67541        67541            2
       67542                        12

--檢視obj$的定義：/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sql.bsq.

  type#         number not null,                 /* object type (see KQD.H): */
  /* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
             /* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
              /* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
      /* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
                                             /* 23 = DIRECTORY , 24 = QUEUE, */
    /* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
    /* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
                 /* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
                                                  /* 35 = INDEX SUBPARTITION */
                                                 /* 82 = (Data Mining) MODEL */

--type# =12  是觸發器。取的第一條記錄實際上是觸發器，也可以得到一個結論oracle刪除表時，先刪除觸發器在刪除表(注輸出按照
--dropscn排序)。這個問題應該是10g下的bug。

3.在11g下重複做一次跟蹤看看。

SCOTT@test> @10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@test> PURGE TABLE T;
Table purged.

SCOTT@test> @10046off
Session altered.

=====================
PARSING IN CURSOR #182926668960 len=228 dep=1 uid=0 oct=3 lid=0 tim=1441703939767161 hv=89057496 ad='be23c848' sqlid='4ah93ms2nxu6s'
select o.obj#, o.name, rb.original_name    from obj$ o, RecycleBin$ rb    where o.obj#=rb.obj# AND rb.owner#=:1 AND      (rb.original_name=:2 OR (o.owner#=rb.owner# AND o.name=:3 )) AND      o.type# = :4      order by rb.dropscn
END OF STMT
PARSE #182926668960:c=1000,e=585,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1441703939767159
BINDS #182926668960:
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=112 off=0
  kxsbbbfp=2a974767d0  bln=22  avl=02  flg=05
  value=84
Bind#1
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
  kxsbbbfp=2a974767e8  bln=32  avl=01  flg=01
  value="T"
Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=56
  kxsbbbfp=2a97476808  bln=32  avl=01  flg=01
  value="T"
Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=88
  kxsbbbfp=2a97476828  bln=22  avl=02  flg=01
  value=2
EXEC #182926668960:c=8999,e=8589,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=752444359,tim=1441703939775858
FETCH #182926668960:c=0,e=256,p=0,cr=14,cu=0,mis=0,r=1,dep=1,og=4,plh=752444359,tim=1441703939776179
STAT #182926668960 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=14 pr=0 pw=0 time=273 us cost=20 size=148 card=2)'
STAT #182926668960 id=2 cnt=1 pid=1 pos=1 obj=0 op='CONCATENATION  (cr=14 pr=0 pw=0 time=232 us)'
STAT #182926668960 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=7 pr=0 pw=0 time=127 us)'
STAT #182926668960 id=4 cnt=1 pid=3 pos=1 obj=0 op='NESTED LOOPS  (cr=6 pr=0 pw=0 time=105 us cost=15 size=74 card=1)'
STAT #182926668960 id=5 cnt=2 pid=4 pos=1 obj=141 op='TABLE ACCESS BY INDEX ROWID RECYCLEBIN$ (cr=2 pr=0 pw=0 time=55 us cost=2 size=481 card=13)'
STAT #182926668960 id=6 cnt=2 pid=5 pos=1 obj=144 op='INDEX RANGE SCAN RECYCLEBIN$_OWNER (cr=1 pr=0 pw=0 time=31 us cost=1 size=0 card=13)'
STAT #182926668960 id=7 cnt=1 pid=4 pos=2 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=4 pr=0 pw=0 time=30 us cost=0 size=0 card=1)'
STAT #182926668960 id=8 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=1 pr=0 pw=0 time=13 us cost=1 size=37 card=1)'
STAT #182926668960 id=9 cnt=1 pid=2 pos=2 obj=0 op='NESTED LOOPS  (cr=7 pr=0 pw=0 time=85 us)'
STAT #182926668960 id=10 cnt=1 pid=9 pos=1 obj=0 op='NESTED LOOPS  (cr=6 pr=0 pw=0 time=63 us cost=4 size=74 card=1)'
STAT #182926668960 id=11 cnt=2 pid=10 pos=1 obj=141 op='TABLE ACCESS BY INDEX ROWID RECYCLEBIN$ (cr=2 pr=0 pw=0 time=23 us cost=2 size=37 card=1)'
STAT #182926668960 id=12 cnt=2 pid=11 pos=1 obj=144 op='INDEX RANGE SCAN RECYCLEBIN$_OWNER (cr=1 pr=0 pw=0 time=11 us cost=1 size=0 card=13)'
STAT #182926668960 id=13 cnt=1 pid=10 pos=2 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=4 pr=0 pw=0 time=27 us cost=1 size=0 card=1)'
STAT #182926668960 id=14 cnt=1 pid=9 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=1 pr=0 pw=0 time=11 us cost=2 size=37 card=1)'
CLOSE #182926668960:c=0,e=409,dep=1,type=0,tim=1441703939776636
=====================

--注意看11g下前面執行的語句加入了一個條件 o.type# = :4 ，引數:4 = 2 ,型別對應的是表。再次證明這個在10g下是一個bug。

4.這個問題實際上可以進一步引申在10g同名索引也會出現問題。

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> flashback table t to before drop;
Flashback complete.

--注意恢復後，觸發器名字不會是T，而是像BIN$HzmVenGmJJzgUKjAWWQSzA==$0名字。

SCOTT@test> drop trigger "BIN$HzmVenGmJJzgUKjAWWQSzA==$0";
Trigger dropped.

SCOTT@test> create index t on t(id);
Index created.

--現在建立了於表同名的索引。

SCOTT@test> DROP TABLE T;
Table dropped.

SCOTT@test> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- ----------------------------------------
BIN$HzmVenGoJJzgUKjAWWQSzA==$0 T                                INDEX
BIN$HzmVenGpJJzgUKjAWWQSzA==$0 T                                TABLE

SELECT o.obj#, o.DATAOBJ#,o.name, rb.original_name,rb.dropscn
    FROM sys.obj$ o, sys.RecycleBin$ rb
   WHERE     o.obj# = rb.obj#
         AND rb.owner# = 57
         AND (rb.original_name = 'T' OR (o.owner# = rb.owner# AND o.name = 'T'))
ORDER BY rb.dropscn;

        OBJ#     DATAOBJ# NAME                           ORIGINAL_NAME                         DROPSCN
------------ ------------ ------------------------------ -------------------------------- ------------
       67543        67543 BIN$HzmVenGoJJzgUKjAWWQSzA==$0 T                                 13200213853
       67541        67541 BIN$HzmVenGpJJzgUKjAWWQSzA==$0 T                                 13200213856

--注意dropscn。第1個物件肯定是索引，DATAOBJ#= 67543。

SCOTT@test> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

--問題再次出現！