在删除临时表时,遇到如下错误:
ORA-14452, "attempt to create, alter or drop an index on temporary table already in use"。
Metalink解决方法如下:
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.