Ask the Expert

Temp file keeps getting larger

In our "live" Oracle 9i database, we have a temp file of 2 GB. It has steadily become larger and larger. I know restarting the database initially resets it to 0, but within no time at all it reaches capacity again (currently 99.5%). I am worried about increasing the size because of possible limitations AIX filesize (2 GB?). I don't know if recreating the tablespace would get rid of the problem or merely put it off for a bit. I thought about recreating it but was uncertain if I was doing the right thing.

The suggestion I found was:

 CREATE TEMPORARY TABLESPACE temp2 TEMPFILE
 '/oracledata/IFSD/temp2_01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M
 MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
 
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
 
 DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
 
 CREATE TEMPORARY TABLESPACE temp TEMPFILE
 '/oracledata/IFSD/temp01.dbf'
 SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT
 MANAGEMENT LOCAL UNIFORM SIZE 1M;
 
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
 
 DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
I am not sure about "local" since I hear of locally managed tablespaces. Perhaps this is what I need?


    Requires Free Membership to View

It might be that you simply need a TEMP tablespace that is larger than 2 GB. You can query V$SORT_USAGE to see if this is the case.

If your OS limits you to 2 GB files, then you can create your TEMP tablespace with multiple tempfiles. This can be performed as follows:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/directory/temp01.dbf' SIZE 2GB
EXTENT MANAGEMENT LOCAL UNIFORM SIZE xxK;   

ALTER TABLESPACE temp ADD 
TEMPFILE '/directory/temp02.dbf' SIZE 2GB;
You can add as many tempfiles as you want. The CREATE TABLESPACE will create a Locally Managed Tablespace with each extent size being a uniform xx KB in length. The value for xx should be the same value as your SORT_AREA_SIZE parameter.


This was first published in July 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: