Resize temporary tablespace

In some Database configuration, temporary tablespace is set to autoextend. When user runs query with lot of sort or any cartesian join, then temp tablespace will increase. If the temp file gets increasingly large, then DBA wanted to resize the tablespace to more reasonable size in order to reclaim the space.

There may be multiple methods exist to reclaim the used space depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original resonable size.

This excerise is tested in oracle9i on Windows. It should apply to unix too.
Step 1.

Create the new temporary tablespace.

SQL> create temporary tablespace temp1 tempfile 2 'E:\ORADATA\RMS30MC\TEMP02.DBF' SIZE 500M 3 autoextend off
4 /
Tablespace created.

Step 2
Assign the newly created tablespace to default temporary tablespace

SQL> alter database default temporary tablespace temp1;
Database altered.

SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------TEMP1

Now all the newly connected users(users conncted to the databaseafter completing step2) will use the temp tablespace TEMP1.

Step 3
Wait until no one is using the temp tablespace.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
and
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

return 0 rows before dropping the old TEMP tablespace.

Step 4
Once we are certain that, no one is using the old temp tablespace,we drop the old temp tablespace.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.