代码改变世界

ORA-14452的出现原因解析及解决方法

  潇湘隐者  阅读(22725)  评论(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/

编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示