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 = '&temp_table_name');

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

StackExchange Database Administrators Q#108486, answer score: 4