ORA-14452

ORA-14452[@more@]关于ORA-14452的解释,这个错误提示是正常的:

The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.1.0.2
This problem can occur on any platform.

Errors
ORA-14452 attempt to create, alter or drop an index on temporary table a

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 the 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.

Fix
Perform a truncate table before dropping the table.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7185924/viewspace-995800/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7185924/viewspace-995800/