RE: temp tablespace blow up

Lyall,  

Extents from temporary sort segments are allocated to one sessions at a time only, and are not deallocated until that session frees them. They are not shared between sessions.  

I think it would be reasonable to set extent size = 1 Mbyte , and set PCTINCREASE=0 and INITIAL=NEXT (this way freed extents will match the next request extent size, and no need for SMON to consolidate freed extents, I think). Intermintently, run a  

select sid, serial#, vs.osuser, extents, contents, blocks from v$session vs,

    v$sort_usage su where vs.saddr = su.session_addr ;  

to learn session's usage.  

In 8.1 there are much more options (EXTENT MANAGEMENT LOCAL/uniform extent size).  

¿You cannot upgrade?  

regards

 --  

If the temp tablespace is going to grow quickly, it's because of some (or somessss) query that need to run through a lot of rows, right? Wouldn't you want as few extents in the temp tablespace as possible if this is going to happy? Why not make the TEMP tablespace one big extent?  

Lyall

--
http://www.freelists.org/webpage/oracle-l