LLI Wiki — My Digital Knowledge Base

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
Mastodon LLIMastodon SocialFosstodon