Resolving Oracle large temp tablespace size problem

Temporary tablespaces are used by the system to sort out things that it cannot do in memory. For example, if you join two very large tables that exceed the memory capacity, Oracle will use the temp tablespace to perform the sorting operation in the temp tablespace. Now, imagine the case in which an user performs a Cartesian join by mistake; the temp tablespace may grow to be a very large size in order to cope with the data. Once the operation is completed, Oracle will not automatically reduce the file size, which what I observed with my Oracle 10g database.

To resolve the issue, I performed the following 6 steps. This solution does not require system restart, but it is recommend that you perform this during slower hours if possible. Temp tablespace does not hold any permanent objects, so I do not back up my temp tablespace, but as always, I recommend that you make certain you have a very recent backup set available to you before you start working, just in case something goes wrong.

1. Create a new temp tablespace

create temporary tablespace temp2
tempfile '/filepath/TEMP02.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

2. Make the new temp tablespace the primary one

alter database default temporary tablespace temp2;

3. Drop the runaway tempfile of the original temp tablespace

alter database tempfile '/filepath/TEMP01.DBF' 
drop including datafiles;

4. Create a new tempfile for the original temp tablespace

alter tablespace temp
add tempfile '/filepath/TEMP01.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

5. Make the original temp tablespace the primary one again

alter database default temporary tablespace temp;

6. Drop the new temp tablespace we created in #1 as we no longer need it

drop tablespace temp2 including contents and datafiles;

At this point, I saw that the disk space is recovered, and the tempfile is at the modest size of 1gb. All the file paths and file sizes are for demonstration purposes only; you should adjust the commands according to your needs.

Not sure where your temp files are? Try the following select statement:

select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS#  RFILE# STATUS  ENABLED         BYTES  BLOCKS CREATE_BYTES BLOCK_SIZE NAME
----- ---------------- --------- --- ------- ------- ---------- ---------- ------- ------------ ---------- --------------------
    1                0             3       1 ONLINE  READ WRITE 1048576000  128000   1048576000       8192 /FILEPATH/TEMP01.DBF