In Oracle when you try to drop a Global Temporary Table that is in use you get the following exception:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
This is caused by a session that is using the GTT in a current transaction. Is there a way to query the data dictionary to determine which sessions are blocking the drop? I know I can get the sessions with in progress transactions with something like this, but I would like to narrow it down further.
select * from v$session
where session_id in (select session_id from dba_locks where lock_type='Transaction');
Best Answer
The session(s) using the temporary table can be queried as:
Then you can kill these sessions or have them release the locks gracefully.