This error appears when you try to issue a DDL against a global temporary table thats created with “on commit preserve rows” clause.:-

create global temporary table TEST (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Such tables get bound to the session with the first insert and then this binding is ONLY removed when the session exits or by truncate. If the binding exists DDL cannot be performed on these temporary tables and if you try a DDL on such tables you would receive ORA-14452 attempt to create, alter or drop an index on temporary table already in use.

In Session1: We would Insert a record to the global temporary table, as per the theory Session1 should be bind to the temporary table and no other session (regardless of the privilege) would be able to perform DDL on the temp table.

Sesssion1:

SQL> insert into TEST values ('1','V');
 1 row created.
 SQL> commit;
 Commit complete.

Now from Session2 we would try to drop the temporary table.

Session2:

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

As expected we received ORA-14452, now there are two ways we could fix this:-

Solution1: Truncate the temp table from Session1 and then drop the table from any session:-

Session1:

SQL> truncate table TEST;
Table truncated.

Session2:

SQL> drop table TEST;
Table dropped.

Solution2: Identify the session thats bind with the global temporary table and then kill the session and then drop the table.

Below query can be used to identify the session thats bind with GTT:-

SQL> select  *
   from  v$lock
   where id1 = (
                select  object_id
                  from  dba_objects
                  where owner = 'SYS' -- Change the owner according to your env.
                    and object_name = 'TEST' -- Change the Table name as per your use case.
               );

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
 
 00000002A18073F8 00000002A1807450         12 TO   44349871          1          3          0         81          0

Now this session can be killed using ALTER SYSTEM KILL SESSION, you can use following query to create a kill command:-

select 'alter system kill session ' || ''''|| sid || ',' || serial# || ''' ;' "Kill_Command" from v$session where sid in (12);

Once the bind session is killed you can drop the temporary table from any session.