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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top