Oracle9i and above: How to reclaim (empty) TEMP tablespace

On the last Saturday, my company peoplesoft Oracle data faced TEMP tablespace used until all free harddisk space finished.

I used below command to solve my problem.

Drop Tempfile Command Method - (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with this method, it should be performed during off hours with no users logged on performing work.

The first step is to obtain the name of the tempfile to drop.

For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp2_01.dbf:

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

------------------------------------------------------------------------------------------------------------

TEMP /u02/oradata/TESTDB/temp01.dbf 13,107,200,000

The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment.

A bad query, however, increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space.

In the example below, I simply drop and recreate the tempfile:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Tablespace altered.


SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

--------------------------------------------------------------------------------------------------------

TEMP /u02/oradata/TESTDB/temp01.dbf 536,870,912