Oerr ORA-14452

解决ORA-14452错误

最新推荐文章于 2024-07-30 08:30:00 发布

weixin_33739541

最新推荐文章于 2024-07-30 08:30:00 发布

阅读量97

点赞数

CC 4.0 BY-SA版权

文章标签： python 数据库

原文链接：https://my.oschina.net/liuliufa/blog/61156

本文介绍了一个关于Oracle数据库中global temporary table的常见问题。当尝试删除已使用的带有on commit preserve rows选项的临时表时，可能会遇到ORA-14452错误。文章详细解释了错误的原因，并提供了具体的解决方案。

2019独角兽企业重金招聘Python工程师标准>>>

前几日，某开发人员对我说，他使用自己的DB账号创建了一张global temporary table，使用完后无法使用该账号进行删除。报如下错误：

“ora-14452 attempt to create alter or drop an index on temporary table already in use”

查阅文档ID 270645.1如下：

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.1.0.6
Information in this document applies to any platform.
***Checked for relevance on 15-Dec-2009***

Symptoms
Attempting to drop a global temporary table results in the following error:
ORA-14452, "attempt to create, alter or drop an index on temporary table already in use"

For example:

SQL> CREATE GLOBAL TEMPORARY TABLE t1 ON COMMIT PRESERVE ROWS
AS SELECT * FROM v$parameter;
Table created.

SQL> DELETE FROM t1;
258 rows deleted.

SQL> DROP TABLE t1;
drop table t1
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Cause
Reading the comments about ORA-14452:

From oerr ora-14452
14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
// *Cause: An attempt was made to create, alter or drop an index on temporary
// table which is already in use.
// *Action: All the sessions using the session-specific temporary table have
// to truncate table and all the transactions using transaction
// specific temporary table have to end their transactions.

Do a truncate table before dropping the table.The documentation on this situation is not very clear:
This is expected behaviour with clause "on commit preserve rows".
DDL cannot be performed on these temporary tables which are in use or bound to a session.
These tables get bound to the session with the first insert. The binding is removed when the session exits or by truncate.

Solution
Perform a truncate table before dropping the table.

SQL> TRUNCATE TABLE t1;
Table truncated.

SQL> DROP TABLE t1;
Table dropped.

转载于:https://my.oschina.net/liuliufa/blog/61156