RECREATE TEMP TABLESPACE

--Create Temporary Tablespace Temp2 /dba0A/oradata/R12D1/
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 
'/dba0A/oradata/R12D1/temp15.dbf' SIZE 50M;

--Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

--Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

--Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 
'/dba0A/oradata/R12D1/temp01.dbf' SIZE 10GB;

--Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

--Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;



If it takes too long

use this to identify queries holding it up.

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;

and then

ALTER SYSTEM KILL SESSION 'sid,serial#';