Shrinking an Oracle temporary tablespace
Procedure to recreate the tempfile of an Oracle temporary tablespace that has grown too large.
When a tempfile of the default temporary tablespace has grown too large, you can recreate it: create a temporary replacement tablespace, make it the default, drop the oversized tempfile, add a new tempfile to the original tablespace, make the original the default again and drop the replacement.
1. Create a new temporary tablespace
create temporary tablespace temp2 tempfile '/u01/oradata/appdb/temp02.dbf' size 1000m autoextend on next 100m maxsize 4000m;
2. Make the new temporary tablespace the default
alter database default temporary tablespace temp2;
3. Drop the runaway tempfile of the original temporary tablespace
alter database tempfile '/path/to/runaway_temp01.dbf'
drop including datafiles;
4. Add a new tempfile to the original temporary tablespace
alter tablespace temp add tempfile '/u01/oradata/appdb/temp01.dbf' size 1000m autoextend on next 100m maxsize 4000m;
5. Make the original temporary tablespace the default again
alter database default temporary tablespace temp;
6. Drop the replacement temporary tablespace
The replacement is no longer needed:
drop tablespace temp2 including contents and datafiles;
Finding the temporary files
To locate the tempfiles of the temporary tablespaces:
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 /u01/oradata/appdb/temp01.dbf