Dropping Global Temporary Tables 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');
The session(s) using the temporary table can be queried as:

select sid from v$lock where type = 'TO' 
and id1 = (select object_id from dba_objects where object_name = '&amp;temp_table_name');


Then you can kill these sessions or have them release the locks gracefully.