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

Cause :- This error occur when you are try to drop Global Temporary Table which are locked.

Solution :- To solve this error you need to truncate table / kill this session / or exit form session and login again.


SQL> create global temporary table temp_data(id number, name varchar2(200)) on commit  PRESERVE ROWS;

Table created.

SQL> insert into temp_data values(10, 'Rajib');

1 row created.

SQL> select * from temp_data;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
Rajib


SQL>  commit;

Commit complete.

SQL>  select * from temp_data;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
Rajib


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


SQL> SELECT SID  FROM V$LOCK
  2  WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
  3  WHERE OWNER='RND'
  4  AND OBJECT_NAME='TEMP_DATA');

       SID
----------
       195

SQL> TRUNCATE TABLE temp_data;

Table truncated.

SQL>  drop table temp_data;

Table dropped.

SQL> SELECT SID  FROM V$LOCK
  2  WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
  3  WHERE OWNER='RND'
  4  AND OBJECT_NAME='TEMP_DATA');

no rows selected

SQL>