PCT_Increase in Temp Tablespace

[rsuri]

Hi All,
In a DSS system (Oracle8i on NT) I have a big table (about 3GB) and a 4GB temporary tablespace (that is made up of 2 datafiles of 2 GB each)

I got a "ORA-1652: unable to extend temp segment by 207381 in tablespace TEMP1" error

I noticed that the temp tablespace is set with the following storage --

Initial - 40KB (I think it's too low because it's a DSS system)
Next - 40KB
pct_increase - 50%
max_extents - 505 (default value)

I think what is happening is that because of the non-zero pct_increase (causing different-size extents) it does not use all of the first datafile and moves to the second datafile wasting a lot of space; Same thing happens in the second datafile.

I am leaning towards using pct_increase as Zero and initial as 1MB (or more) and using unlimited max_extents

Does anybody have any opinion about using zero pct_increase ?

Thanks,

- Rajeev

[omegamark]

Pls drop that Temp tablespace and create a Locally Managed temp tablespace .Your problem is solved.See to that the extent size is kept to a minimum of 100M.Since this is DSS.

[jgmagnus]

Oracle DBA Handbook recommends a pct increase of zero on all temp tablespaces.

Maximizing the reusability of extents can be accomplished by choosing an INITIAL and NEXT extent size of 1/20th to 1/50th of the size of the actual tablespace.

If all else fails increase the size of the TEMP tablespace in order to accomplish your goal.

- Magnus

[marist89]

I definitely prefer to setup temporary tablespaces with initial 1M next 1M pctincrease 0 maxextents unlimited. Also, if you don't have a good idea of how large TEMP will get, you can set the datafiles to autoextend out to your max file size for the OS.

You may also want to look at locally managed tablespaces. I think implementing locally managed tablespaces in a temporary tablespace would alleviate any extent allocation burden on the system tablespace.

[pando]

if you are using 8i you choice would be locally managed tablespace using uniform clause, make the extent size big and multiple size of sort area size, big so when you shutdown the database it wont take ages.