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