Oracle Temp Tablespace Becomes Too Large – Resize the Temp Tablespace

We mostly find ourselves stuck in the frustrating situation where our temporary tablespace becomes too large and hence database runs out of temporary tablespace or hard disk run out of space. This article provides some great tips that will help you reduce temporary tablespace.

Step 1 - Resizing Tempfile

Resize the tempfile to the reasonable size by running ALTER DATABASE TEMPFILE command

“ALTER DATABASE TEMPFILE '/export/home/oracle/oradata/viom/temp01.dbf' RESIZE RESIZE 250M”

Note: - Specify the temp file full path in above mentioned command.

Above command may results into ORA-03297 error. In such situation you should ensure that the TEMP datafile is autoextensible and maxbytes/maxblocks are not very low

Step 2 - Reducing Tempfile

Below approach will help you reduce tempfile even if your ALTER DATABASE TEMPFILE command results into ORA-03297 error. Mention the correct path in given commands.

1. Create temporary Tablespace: First of all create a temporary tablespace
   
   “CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/export/home/oracle/oradata/viom/temp02_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;” 


2. Make it Default temporary Tablespace: Make it the default temporary tablespace of our database
                
               “ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;”


3. Drop Old temporary Tablespace Datafiles: Drop old temporary tablespace datafiles by running below command.                 
   
   “ALTER DATABASE TEMPFILE '/export/home/oracle/oradata/viom/temp01.dbf' DROP                       INCLUDING DATAFILES;”


4. Add New Datafile: Add the new datafile to our old temporary tablespace.
   
   “ALTER TABLESPACE TEMP ADD TEMPFILE '/export/home/oracle/oradata/viom/temp02.dbf ' SIZE 100M AUTOEXTEND ON NEXT 100M;”


5. Redefine temporary Tablespace: Redefine temporary tablespace as the default temporary tablespace of our Oracle database
   
   “ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;”


6. Drop the New temporary Tablespace: Finally drop the new temporary tablespace created by us.
   
   “DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;”