临时表的ORA-14552错误

在处理临时表的时候经常会碰到这个错误。

 

 

最简单的例子：

SQL> CREATE GLOBAL TEMPORARY TABLE T_TEMP
  2  (ID NUMBER)
  3  ON COMMIT PRESERVE ROWS;

表已创建。

SQL> INSERT INTO T_TEMP VALUES (1);

已创建 1 行。

SQL> DROP TABLE T_TEMP;
DROP TABLE T_TEMP
           *
第 1 行出现错误:
ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

这时无论是ROLLBACK、COMMIT还是删除表中的数据都无法解决问题：

SQL> ROLLBACK;

回退已完成。

SQL> DROP TABLE T_TEMP;
DROP TABLE T_TEMP
           *
第 1 行出现错误:
ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

SQL> DELETE T_TEMP;

已删除 1 行。

SQL> COMMIT;

提交完成。

SQL> DROP TABLE T_TEMP;
DROP TABLE T_TEMP
           *
第 1 行出现错误:
ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

唯一可以使用的方法是先TRUNCATE临时表，然后进行删除：

SQL> TRUNCATE TABLE T_TEMP;

表被截断。

SQL> DROP TABLE T_TEMP;

表已删除。

Oracle在metalink文档Doc ID:  Note:270645.1中描述了这个问题。

如果是其他会话导致了这个问题：

SQL> CREATE GLOBAL TEMPORARY TABLE T_TEMP
  2  (ID NUMBER)
  3  ON COMMIT PRESERVE ROWS;

表已创建。

SQL> INSERT INTO T_TEMP VALUES (1);

已创建 1 行。

在其他的会话试图删除临时表：

SQL> SET SQLP 'SQL2> '
SQL2> DROP TABLE T_TEMP;
DROP TABLE T_TEMP
           *
第 1 行出现错误:
ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

SQL2> TRUNCATE TABLE T_TEMP;

表被截断。

SQL2> DROP TABLE T_TEMP;
DROP TABLE T_TEMP
           *
第 1 行出现错误:
ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

这时要不然等待使用临时表的事务主动是否锁，比如TRUNCATE或者断开会话，要不然就需要通过KILL SESSION的方式来杀掉占有锁的会话：

SQL> DISC
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

会话1断开，会话2就可以删除临时表了：

SQL2> DROP TABLE T_TEMP;

表已删除。

问题在于，Oracle修改ON COMMIT PRESERVE ROWS的临时表后不会主动释放锁，必须要断开会话或者执行TRUNCATE操作才能释放：

SQL> CONN YANGTK/YANGTK@YTK102
已连接。
SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;

       SID
----------
       159

SQL> SELECT * FROM V$LOCK WHERE SID = 159;

未选定行

SQL> CREATE GLOBAL TEMPORARY TABLE T_TEMP
  2  (ID NUMBER)
  3  ON COMMIT PRESERVE ROWS;

表已创建。

SQL> INSERT INTO T_TEMP VALUES (1);

已创建 1 行。

SQL> SELECT * FROM V$LOCK WHERE SID = 159;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
33034450 33034464        159 TO      57573          1          3          0          3          0
32B7DDF4 32B7DF10        159 TX     131115       5879          6          0          3          0

SQL> COL OBJECT_NAME FORMAT A30
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  2  FROM DBA_OBJECTS WHERE OBJECT_ID = 57573;

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ---------------
YANGTK                         T_TEMP                         TABLE

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM V$LOCK WHERE SID = 159;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
33034450 33034464        159 TO      57573          1          3          0         51          0

可以看到，提交之后事务锁已经释放，但是临时表上的锁并没有释放，这就是为什么执行DROP时会报错。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-421347/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-421347/