ORA-14452

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

Query :- Drop Global Temporary Table TEMPEMP;

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

When you are getting the above error then use the below command to resolve the issue.

Step 1:- Check the SID where your temporary table is currently using.

Query :- select  *   from  gv$lock   where id1 = (select  object_id from  dba_objects

where owner = ‘SCOTT' and object_name = 'TEMPEMP');

 O/P:- You will get the SID of this locked table, suppose SID=40 then

Step 2:- Check SID and SERIAL# from the session then running session

Query :- select SID,SERIAL#  from gv$session where sid=40;

O/P:-  You will get information about SID and SERIAL# with this query

Step 3:- Now kill the session, Suppose your SID=40 and SERIAL#=23 then

Query:-  Alter System Kill Session ’40,23’ Immediate;

After the execution of this query, your session for the temporary table will be killed.

Now again try to drop the temporary table i.e TEMPEMP this time your temporary table will be dropped..!! J