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

最简单的例子：

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时会报错。

好！