Resizing Temporary Tablespace

Sometimes, in many database configuration, the DBA will choose to allow their temporary tablespace to AUTOEXTEND. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space.
If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space.
The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

The best practice are:
- create another temporary tablespace
- make the new temporary tablespace as the default temporary tablespace
or if you want to use the old tablespace name
- drop the old temporary tablespace, and recreate it, set it as the default temporary tablespace
- drop the other temporary tablespace

Example:
SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

NOTE:
On some platforms , it is possible for the tempfile to be deleted from DBA_TEMP_FILES but not from the hard drive of the server.

If this occurs, simply delete the file using regular O/S commands.