Yesterday suddenly I received a call from my guys that one of the database server at my client’s place  has only 56 MB space left in “C” drive.

I was shocked to hear it as a day before yesterday it has 15GB of free space in the “C” drive.

After investigation I found out that the temp tablespacce in “C” drive has reached more than 30 GB.

On further investigation, I found out that it has happened as lot of “data warehousing” kinds of reports (candidate for data warehousing) were designed to run in present database design which is originally mean for OLTP activities. However this is not the core of discussion in my post.

My main point of discussion is how to reduce this temp tablespace.

On internet I found lot of questions on it, such as:

alter database tempfile ‘/u01/oradata/PROD/temp01.dbf’ resize 1000m;

it produces ORA-03297: file contains used data beyond requested RESIZE
value

So is the any way to drop/resize/”re-initialize” …..

another one

RE: TEMP TABLESPACE BECOMES TOO LARGE

and another one

What You Can Do When Your Database Runs out of Temp Space

So I though of sharing my experience and solution with others

This is how I normally solve this problem

Info about the temp tablespace of an example in this post:

First Way:

ALTER DATABASE TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF’ RESIZE 100M;

If the above fails then go for second way that is:

Create another temporary tablespace as follows:

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE <>
SIZE 100M AUTOEXTEND ON NEXT 10M
MAXSIZE 2000M;

Define it as a default temporary tablespace of the database

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Drop old temporary tablespace datafiles

ALTER DATABASE TEMPFILE ‘<>’ DROP INCLUDING DATAFILES;

After that add new datafile to old temporary tablespace:

ALTER TABLESPACE temp
ADD TEMPFILE  <>
SIZE 100M AUTOEXTEND ON NEXT 10M
MAXSIZE 20000M;

Redefine old temporaray tablespace as a default temporary tablespace of the database

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Drop the new temporary tablespace you have created:
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;