Drop Temporary Tablespace Hangs

Problem Description
The DROP temporary tablespace operations take long time and in fact it hangs. If you take a 10046 trace of the session it shows "enqueue" wait.

Cause of The Problem In the section http://hemora.blogspot.com/2008/09/operation-that-require-sort-area-or.html I discussed about the operation that needs sort space. Whenever an operation is using sort space an entry is found in the v$sort_usage. After the operation finishes entry from $sort_usage vanishes. But dead connections (while running a query) may leave entries in v$session (status inactive) and in v$sort_usage. The query about the users who is Performing Sort operation in Temp Segments can be found in http://hemora.blogspot.com/2008/09/information-about-temporary-segments.html
Solution of The Problem
1.Create a new temporary tablespace and assign all users to this new tablespace. You can easily do this task on unix system as,
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 100m;
Change it for all by
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

2.Find out the all the sessions that are not active and have an entry in V$sort_usage.
You can do it by,
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

3.Kill those session.
Using
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually.
where SID_NUMBER and SERIAL#NUMBER is found in step 2.

4. Now dropping the previous tablespace
DROP TABLESPACE previous_temp_tbs;

The operation that require sort area or Temporary Tablespace

Whenever a sort occurs within a database it needs sort area. Primarily memory area is used to sort. If there is not sufficient memory then it is needed temporary segments where database writes data in order to sort. There are several operation which needs sort space. They are,

1)Index creation.
The CREATE INDEX statement causes the server process to sort the index values before building the tree. After the sort a final index is built in the tablespaces by using a temporary segment.

2)ORDER BY or GROUP BY clauses of SELECT statements.
The server process must sort on the values in the ORDER BY or GROUP BY clauses.

3)DISTINCT values of SELECT statements.
For the DISTINCT keyword, the data is at first sorted in order to eliminate duplicates.

4)UNION, INTERSECT or MINUS operations.
Servers need to sort the tables they are working on to eliminate duplicates.

5)Sort-Merge joins.
If no index is available, an equivalent-join request needs to perform full table scans and sort each row source separately. After that, the sorted sources are merged together, combining each row from one source with each matching row of the other source.

6)Analyze command execution.
The Analyze command sorts the data to provide summarized information.

7)Various SQL Statements.
The CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, and CREATE TABLE statements require sort segment.

8)CREATE TABLE AS SELECT.
The creation of a new table can start as a temporary segment if MINEXTENTS is larger than 1 or when using the statement CREATE TABLE AS SELECT.