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

 
This is caused by another session holding a lock on the object in question.

As per oerr error facility:

oerr ora 14452
14452, 00000, “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.
// *Action: 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.

To identify the session that is holding the lock you can try one of these queries.
select * from v$access where object = 'object_being_dropped';
or
select *
from v$lock
where id1 = (select object_id
from dba_objects
where owner = 'schema_owner_of_object_being_dropped'
and object_name = 'object_being_dropped');

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart