DBA Tips for Oracle Temp Tablespace Growth Issue!!
Oracle Temp Tablespace Becomes Too Large - Database Runs Out Of Temp Tablespace

 Author: Mark D. Roger

TEMP tablespace growth issue is known issue for DBAs. Being database administrators we mostly find ourselves stuck in the frustrating situation where our temporary tablespace becomes too large and hence database runs out of temporary tablespace. In this article we will discuss some great tips that will help you reduce temporary tablespace.

Getting Information about Temporary Tablespace:

Below command can help you get information about the temporary tablespace.

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_TEMP_FILES;

TABLESPACE_NAME                                     FILE_NAME
            TEMP                   D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF

Resizing Tempfile:

In most cases we try to reclaim extra space by resizing temporary tablespace to a more reasonable size by running ALTER DATABASE TEMPFILE command.

ALTER DATABASE TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' RESIZE RESIZE 250M

Trying to resize temporary files often results into ORA-03297 error. In such situation you should ensure that the TEMP datafile is autoextensible and maxbytes/maxblocks are not very low. Similarly you should identify and tune those SQL queries that are responsible for allocating big chunk of TEMP tablespace.

ALTER DATABASE TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' RESIZE RESIZE 250M
*
ERROR AT LINE 1:
ORA-03297: FILE CONTAINS USED DATA BEYOND REQUESTED RESIZE VALUE

Reducing Tempfile:

Below approach will help you reduce tempfile even if your ALTER DATABASE TEMPFILE command results into ORA-03297 error.

Create temporary Tablespace:

First of all we will create a temporary tablespace

CREATE TEMPORARY TABLESPACE MYTEMPFILE
TEMPFILE D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01_01.DBF'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Make it Default temporary Tablespace:

Now we will make it the default temporary tablespace of our database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMPFILE;

Drop Old temporary Tablespace Datafiles:

Now we will drop our old temporary tablespace datafiles by running below command.

ALTER DATABASE TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;

Add New Datafile:

Now we will add the new datafile to our old temporary tablespace.

ALTER TABLESPACE TEMP
ADD TEMPFILE  ‘ D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF '
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 20000M;

Redefine Old temporary Tablespace:

Now we will redefine our old temporary tablespace as the default temporary tablespace of our Oracle database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Drop the New temporary Tablespace:

Finally we will drop the new temporary tablespace created by us.

DROP TABLESPACE MYTEMPFILE INCLUDING CONTENTS AND DATAFILES;

Related Articles:

1. Inside Oracle Temporary Tablespaces!!
2. Great Tips on solving Temporary Tablespace Problems!
3. Inside Oracle Temporary Tables!!

Click here for more Oracle Articles

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Sorting for Oracle Tuning
   Oracle10g Wait Event Tuning
   Why do we need a good database design?
   Oracle Management Goals: Scalable Oracle Web Systems