[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下会报错。
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
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'
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
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
oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=56
kxsbbbfp=7fa1867e6018 bln=32 avl=01 flg=01
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> @10046off
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
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:
oacflg=00 fl2=0001 frm=00 csi=00 siz=112 off=0
kxsbbbfp=2a974767d0 bln=22 avl=02 flg=05
value=84
kxsbbbfp=2a974767e8 bln=32 avl=01 flg=01
kxsbbbfp=2a97476808 bln=32 avl=01 flg=01
Bind#3
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> 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.
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
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
--问题再次出现！
![Java String.format方法的简单使用[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)