ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Cause: Tried to drop a GTT Global temporary table but I got an error 'ORA-14452: attempt to create, alter or drop an index on temporary table already in use'.

Here problem with not completed the transaction in any session and tried to drop the table then we will get this error. For session specifict GTT, one created with ON COMMIT PRESERVE ROWS, the session needs to truncate the GTT.

Action: Followed below steps to DROP GTT

1) Tried to drop a GTT

SQL> drop table REGISTR.ENT_GTT;
drop table REGISTR.ENT_GTT
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
SQL>

2) First we need to verify If any active session is locking this object, If yes, then we can find the SID of that locked object.

SQL> select  *   from  v$lock   where id1 = (select  object_id from  dba_objects where owner = 'REGISTR' and object_name = 'ENT_GTT');

SID is 447.

3) Kill the session using the SID

SQL> select inst_id,'alter system kill session ''' || SID || ',' || SERIAL# || ''' IMMEDIATE;' from gv$session gg where gg.SID =447;

SQL> alter system kill session '447,4045' IMMEDIATE;

System altered

4) Now I dropped the GTT with out any issues

SQL> drop table REGISTR.ENT_GTT;

Table dropped

SQL> 

That's it..Hope this will help you...:)

Best Regards,
Chowdari