Oracle临时表ORA-14552错误
2008-09-02 12:47:13 来源:WEB开发网在处理临时表的时候经常会碰到这个错误。
最简单的例子:
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时会报错。
更多精彩
赞助商链接