Tag: ORA-14452

Error :
 ORA-14452: 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.

Solution :
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.

Example :

SQL>CREATE GLOBAL TEMPORARY TABLE Osama ON COMMIT PRESERVE ROWS
AS SELECT * FROM employee ;

SQL> DELETE FROM Osama;
258 rows deleted.

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

Fixing :

SQL> TRUNCATE TABLE Osama;
Table truncated.

SQL> DROP TABLE Osama;
Table dropped.

Done & Enjoy 

Osama Mustafa