ORA-14452的出现原因解析及解决方法
2014-03-26 23:46 潇湘隐者 阅读(22298) 评论(1) 编辑 收藏 举报在删除临时表时遇到了ORA-14452错误:ORA-14452: attempt to create , alert or drop an index on temporary table already in use。第一次碰到这种情况,问题解决过后,为了加深理解,特意参考网上资料,做了下面实验重现错误出现的场景,以及应该如何解决.
会话级临时表
由于实验需要两个或多个会话配合,所以使用SET SQLPROMPT来标识会话,如下所示,SESSION 1与SESSION 2:
Step 1:在会话1中创建了会话级的临时表TMP_TEST
1 SQL> SET SQLPROMPT "SESSION 1 >" 2 SESSION 1 >CREATE GLOBAL TEMPORARY TABLE TMP_TEST 3 2 ( NAME VARCHAR2(12) ) ON COMMIT PRESERVE ROWS; 4 5 Table created. 6 7 SESSION 1 >INSERT INTO TMP_TEST VALUES('Kerry'); 8 9 1 row created. 10 11 SESSION 1 >COMMIT; 12 13 Commit complete. 14 15 SESSION 1 >SELECT * FROM TMP_TEST; 16 17 NAME 18 ------------ 19 Kerry
Step 2:打开另外一个会话2,在这个会话里面操作临时表TMP_TEST,插入数据。
1 SQL> SET SQLPROMPT "SESSION 2 >" 2 SESSION 2 >SELECT * FROM TMP_TEST; 3 4 no rows selected 5 6 SESSION 2 >INSERT INTO TMP_TEST VALUES('Jimmy'); 7 8 1 row created. 9 10 SESSION 2 >SELECT * FROM TMP_TEST; 11 12 NAME 13 ------------ 14 Jimmy
Step 3: 在会话1中删除临时表时,就会出现ORA-14452错误。
1 SESSION 1 >TRUNCATE TABLE TMP_TEST; 2 3 Table truncated. 4 5 SESSION 1 >DROP TABLE TMP_TEST; 6 DROP TABLE TMP_TEST 7 * 8 ERROR at line 1: 9 ORA-14452: attempt to create, alter or drop an index on temporary table already in use
Step 4: 如果在会话2中先清空数据,然后去会话1中删除表则可顺利完成
1 SESSION 2 >TRUNCATE TABLE TMP_TEST; 2 3 Table truncated. 4 5 6 SESSION 1 >DROP TABLE TMP_TEST; 7 8 Table dropped.
事务级临时表
Step 1: 在会话1中创建事务级全局临时表。
1 SESSION 1 >CREATE GLOBAL TEMPORARY TABLE TMP_TEST 2 2 ( NAME VARCHAR2(12) ) ON COMMIT DELETE ROWS; 3 4 Table created. 5 6 SESSION 1 >INSERT INTO TMP_TEST 7 2 VALUES('Kerry'); 8 9 1 row created. 10 11 SESSION 1 >SELECT * FROM TMP_TEST; 12 13 NAME 14 ------------ 15 Kerry 16 17 SESSION 1 >COMMIT;
Step 2:在会话2中插入一条记录。
1 SESSION 2 >INSERT INTO TMP_TEST VALUES('Jimmy'); 2 3 1 row created. 4 5 SESSION 2 >SELECT * FROM TMP_TEST; 6 7 NAME 8 ------------ 9 Jimmy
Step 3: 在会话3中删除全局临时表时就会报错
1 SESSION 1 >SELECT * FROM TMP_TEST; 2 3 no rows selected 4 5 SESSION 1 >TRUNCATE TABLE TMP_TEST; 6 7 Table truncated. 8 9 SESSION 1 >DROP TABLE TMP_TEST; 10 DROP TABLE TMP_TEST 11 * 12 ERROR at line 1: 13 ORA-14452: attempt to create, alter or drop an index on temporary table already in use
Step 4: 在会话2中提交后,即可在会话1中删除全局临时表。
SESSION 2 >COMMIT;
Commit complete.
总结:不管事务级还是会话级的临时表,都需要所有会话解除绑定,才能DROP,解除绑定的办法就是清空每个会话的数据.清空数据的办法:事务级别的临时表:COMMIT/TRUNCATE TABLE;会话级的临时表:TRUNCATE TABLE ;但是很多时候,如果出现了这种错误,但是我们不知道是那个用户的那个会话没有解除绑定,那么此时要如何解决呢?你可以用下面SQL语句来查询那个会话没有解除绑定,然后杀掉会话进程。
1 SELECT SID, SERIAL# FROM V$SESSION V 2 WHERE SID IN (SELECT SID FROM V$LOCK L, DBA_OBJECTS O 3 WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER('TMP_TEST') ); 4 5 ALTER SYSTEM KILL SESSION 'SID, SERIAL#';
或者你使用下面步骤一步一步的去定位那个会话没有解除绑定。
Step 1、先从DBA_OBJECTS/ALL_OBJECTS /USER_OBJECTS中查询到该表的OBJECT_ID:
SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME='TMP_TEST'
Step 2、根据查到的OBJECT_ID知道使用该表的SESSION:
SELECT * FROM V$LOCK WHERE ID1=&OBJECT_ID;
Step 3、在从v$session视图中查到该session的SID和SERIAL#:
SELECT * FROM V$SESSION WHERE SID=&SID;
Step 4、杀掉这些进程:
ALTER SYSTEM KILL SESSION 'SID, SERIAL#';
参考资料:
http://blog.itpub.net/70612/viewspace-1034301/
![](https://images.cnblogs.com/cnblogs_com/kerrycode/1913302/o_240731062102_kerrycode.png)