Q

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?


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 last published in July 2004

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close