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

--问题再次出现！

时间： 2024-10-23 14:37:07

10g包含同名触发器的表执行PURGE TABLE出错的相关文章

包含同名触发器的表PURGE TABLE出错

今天在测试的时候无意中发现了一个10g的小bug.当包含同名触发器的表被放入回收站时,PURGE TABLE会报错. 例子如下: SQL> CREATE TABLE T (ID NUMBER); 表已创建. SQL> CREATE OR REPLACE TRIGGER T BEFORE INSERT ON TFOR EACH ROWBEGINNULL;END;/ 触发器已创建 SQL> DROP TABLE T; 表已删除. SQL> PURGE TABLE T;PURGE TAB

oracle数据库性能调优技术：深入理解单表执行计划

一.概述 这篇文章是数据库性能调优技术的第二篇.上一篇讲解的索引调优是数据库性能调优技术的基础.这篇讲解的深入理解单表执行计划,是数据库性能调优的有力工具. 查询语句可以有多种可选执行计划,如何选择效率最高的执行计划?达梦数据库.oracle数据库.sql server数据库都是采用基于成本的查询优化,对备选执行计划进行打分,选择大家最小的执行计划进行执行.这些内容,我会在后续的几篇文章中进行详细的描述.在此之前,我们首先需要掌握如何理解数据库执行计划.这篇文章讲解只涉及单表操作的执行计划. 达

mysql-MySQL5.0，使用触发器修改表结构

问题描述 MySQL5.0,使用触发器修改表结构 需求: 表t_fault_code记录故障信息编码, 表t_fault_record记录故障记录, 表t_fault_count记录每日的故障统计 现在,code表中增加一条记录时,需要在count表中新增一列,用来存储record表中该故障的出现次数.我的思路: 创建3个触发器, trigger 1:code表insert一条记录后,查询count表中是否已经存在该列(列名:t_fault_code.Source).如果不存在,新增一列. tr

Oracle中用于数据备份的触发器及表结构

运行本文的示例前,请对当前的数据表的插入.删除.更新等进行记录,并把操作日期.操作者.原来数据.现在数据都作为备份,以便在当前备出现问题的时候能够给以恢复. 注释:最好把备份表放在不同的磁盘上或不同的数据库中: 1.建立用于数据备份及操作记录的表: CREATE TABLE AUD_PRGAMD1( W_ROWID VARCHAR2(50), --行ID W_ACTION VARCHAR2(1), --操作 DTTM DATE, --操作日期 OPUSER VARCHAR2(30), --操作者

MyBatis学习教程(二)—如何使用MyBatis对users表执行CRUD操作_java

上一篇文章MyBatis入门学习教程(一)-MyBatis快速入门中我们讲了如何使用Mybatis查询users表中的数据,算是对MyBatis有一个初步的入门了,今天讲解一下如何使用MyBatis对users表执行CRUD操作.在没奔主题之前,先给大家补充点有关mybatis和crud的基本知识. 什么是 MyBatis? MyBatis 是支持普通 SQL 查询,存储过程和高级映射的优秀持久层框架. MyBatis 消除了几乎所有的 JDBC 代码和参数的手工设置以及对结果集的检索.MyBa

Sql Server中的表访问方式Table Scan, Index Scan, Index Seek

  Sql Server中的表访问方式Table Scan, Index Scan, Index Seek 0.参考文献 oracle表访问方式 Index Seek和Index Scan的区别以及适用情况 1.oracle中的表访问方式 在oracle中有表访问方式的说法,访问表中的数据主要通过三种方式进行访问: 全表扫描(full table scan),直接访问数据页,查找满足条件的数据 通过rowid扫描(table access by rowid),如果知道数据的rowid,那么直接通

mysql 命令修改表结构ALTER TABLE 句法

ALTER TABLE 句法 ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_specification:         ADD [COLUMN] create_definition [FIRST | AFTER column_name ]   or    ADD [COLUMN] (create_definition, create_definition,...)   or    ADD INDEX [ind

全局臨時表 GLOBAL TEMPORARY TABLE

1.会话特有的临时表     CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)     ON COMMIT PRESERVE ROWS:   2.事务特有的临时表     CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)     ON COMMIT DELETE ROWS:    CREATE GLOBAL TE

修复MySQL的MyISAM表命令check table用法

MySQL日志文件里出现以下错误,MySQL表通常不会发生crash情况,一般是在更新数据库时MySQL停止会导致. CHECK TABLE语法 CHECK TABLE tbl_name[,tbl_name] ... [option] ... option= {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 检查一个或多个表是否有错误.CHECK TABLE对MyISAM和InnoDB表有作用.对于MyISAM表,关键字统计数据被更新. CHECK TAB