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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close