Oracle临时表ORA-14552错误

核心提示：在处理临时表的时候经常会碰到这个错误，最简单的例子：SQL>createGLOBALTEMPORARYTABLET_TEMP2(IDNUMBER)3ONCOMMITPRESERVEROWS;表已创建，Oracle临时表ORA-14552错误， SQL>insertINTOT_TEMPVALUES(1);已创

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

最简单的例子：

SQL>createGLOBALTEMPORARYTABLET_TEMP　　　2(IDNUMBER)　　　3ONCOMMITPRESERVEROWS;

表已创建。

SQL>insertINTOT_TEMPVALUES(1);

已创建 1 行。　　

SQL>dropTABLET_TEMP;　　　dropTABLET_TEMP

*第 1 行出现错误:

ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

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

SQL>ROLLBACK;

回退已完成。

SQL>dropTABLET_TEMP;　　　dropTABLET_TEMP

*第 1 行出现错误:

ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

SQL>deleteT_TEMP;

已删除 1 行。　　

SQL>COMMIT;

提交完成。

SQL>dropTABLET_TEMP;　　　dropTABLET_TEMP

*第 1 行出现错误:

ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

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

SQL>truncateTABLET_TEMP;

表被截断。

SQL>dropTABLET_TEMP;

表已删除。

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

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

SQL>createGLOBALTEMPORARYTABLET_TEMP　　　2(IDNUMBER)　　　3ONCOMMITPRESERVEROWS;

表已创建。

SQL>insertINTOT_TEMPVALUES(1);

已创建 1 行。

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

SQL>SETSQLP’SQL2>’　　　SQL2>dropTABLET_TEMP;　　　dropTABLET_TEMP

*第 1 行出现错误:

ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

SQL2>truncateTABLET_TEMP;

表被截断。

SQL2>dropTABLET_TEMP;　　　dropTABLET_TEMP

*第 1 行出现错误:

ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

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

SQL>DISC从OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options 断开

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

SQL2>dropTABLET_TEMP;

表已删除。

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

SQL>CONNYANGTK/YANGTK@YTK102已连接。　SQL>selectSIDFROMV$MYSTATwhereROWNUM=1;　　　SID　　　----------　　　159　SQL>select*FROMV$LOCKwhereSID=159;

未选定行

SQL>createGLOBALTEMPORARYTABLET_TEMP　　　2(IDNUMBER)　　　3ONCOMMITPRESERVEROWS;

表已创建。

SQL>insertINTOT_TEMPVALUES(1);

已创建 1 行。

SQL>select*FROMV$LOCKwhereSID=159;　　　ADDRKADDRSIDTYID1ID2LMODEREQUESTCTIMEBLOCK　　　----------------------------------------------------------------------------------------　　　3303445033034464159TO5757313030　　　32B7DDF432B7DF10159TX13111558796030　　　SQL>COLOBJECT_NAMEFORMATA30　　　SQL>selectOWNER,OBJECT_NAME,OBJECT_TYPE　　　2FROMDBA_OBJECTSwhereOBJECT_ID=57573;　　　OWNEROBJECT_NAMEOBJECT_TYPE　　　---------------------------------------------------------------------------　　　YANGTKT_TEMPTABLE　SQL>COMMIT;

提交完成。

SQL>select*FROMV$LOCKwhereSID=159;　ADDRKADDRSIDTYID1ID2LMODEREQUESTCTIMEBLOCK　----------------------------------------------------------------------------------------　3303445033034464159TO57573130510

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