Re: Reached limit on Temp Tablespace Extents

In article <CxAzzC.GvK_at_cisa.bt.co.uk>

           devsl_at_cisa.bt.co.uk "Stephen Laverack" writes:

> In the temporary tablespace, alter the default storage clause settings
> for INITIAL, NEXT and PCTINCREASE. Any temporary object will use these
> storage settings, and therefore you should be able to use the entire
> temp tablespace if need be.
>
> If you then get the error "Failed to allocate extent ..... in temp
> tablespace.."then you will need to increase the size of the tablespace itself

There are two other other things you can do: You could coalesce the free space in the tablespace to give you bigger chunks. This only helps if you've still got enough free space in the tablespace and the blocks are contiguous.

Or, I tend to prefragment the temporary tablespaces in our main Financials instance of 3G. We have about 80 concurrent users with 200M of TEMP space. By altering the tablespace to have 100 equal extents already existing, a user doing a huge sort or join will merely grab more of the prefragged extents and not start demanding demented size extents. This reduces the problem somewhat. You have to be careful to preserve the balance, or you run out of chunks again in TEMP! 8-(  

Hope this confuses matters further!

> Steve
 

-- 
Alligator Descartes 		     |  "Murder, Polis!"
descarte_at_istellar.demon.co.uk        |   		- Bud Neill	
http://halibut.datatamers.com/       |  
-------------------------------------+----------------------------------------
XDBA - An X-based database management tool for ORACLE. 
	Undergoing substantial rewrites - 0.0.2 ( Smurf )