In our "live" Oracle 9i database, we have a temp file of 2 GB. It has steadily become larger and larger. I know...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.