Resizing Temp Tablespace

These are the step by step instructions for resizing a Temp tablespace with AUTOEXTEND ON which has got way too big as a result of a rogue query.
Change filenames and paths accordingly.

The example here is good ebcause the Temp Tablespace is actually made up of two datafiles. I couldnt find any syntax examples for Temp tablespaces with more than one datafile.

## Create another temporary tablespace
CREATE TEMPORARY TABLESPACE TEMPXXX TEMPFILE '/u02/oracle/oradata/EV6PROD/temp03.dbf'
SIZE 1000M REUSE AUTOEXTEND ON NEXT 8K MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

## make it the default temporary tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPXXX;

## drop the old tablespace and datafile to free space - CANNOT TAKE A TEMPORARY TABLESPACE OFFLINE
##DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; hangs

alter database tempfile '/u01/app/oracle/product/10.1.0/oradata/EV6PROD/temp01.dbf','/u02/oracle/oradata/EV6PROD/temp02.dbf' drop including datafiles;
drop tablespace temp;
-- rm /u02/oradata/EVWHP/temp02.dbf

## recreate the old TEMP tablespace
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oracle/oradata/EV6PROD/temp01.dbf'
SIZE 1000M REUSE AUTOEXTEND ON NEXT 8K MAXSIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

## make it the default temporary tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

## drop the new, small tablespace and datafile to free space
DROP TABLESPACE TEMPXXX INCLUDING CONTENTS AND DATAFILES

Some good sources for TEMP Tablespace related stuff:

http://www.orafaq.com/node/2
http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
http://www.oracle.com/technology/oramag/oracle/05-jan/o15dba.html


What can go wrong:

The temp files which were deleted by the command:

alter database tempfile '/u01/app/oracle/product/10.1.0/oradata/EV6PROD/temp01.dbf','/u02/oracle/oradata/EV6PROD/temp02.dbf' drop including datafiles;

will not show when doing a du or ls -l, however df command still shows the space is allocated and not released if a process still thinks its using the file.

lsof unix may show all the processes that still think the file is open but this didnt work for me. At the end I had to bounce the database, so much for trying to do this online :)