Re: TEMP tablespace grows all the time!

Not that this will help you, but here's your first mistake. Locally managed temporary tablespace should have extent sizes of sort_area_size or multiples thereof *without* the extra block, which is only of relevance for dictionary managed tablespace.

Your other mistake is to have autoextend on. Why? Assuming you have sized your temporary tablespace properly (which I expect you haven't), why would you want to allow it to increase for evermore? Take the autoextend off, increase the sort_area_size so you need the temporary tablespace less, and then diagnose the problems as and when particular reports fail due to lack of temporary tablespace. At that point, start looking at the code to see if something more efficient couldn't be done.

Regards
HJR

--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================


"SGA" <zeevikm_at_hotmail.com> wrote in message
news:b0fb5246.0204010037.63d4c78f_at_posting.google.com...


> Thanks Adrian,

> All extents are uniform of course (SORT_AREA_SIZE + DB_BLOCK_SIZE),

> so probably that is not the issue.

>

> Thanks again for your response.

>

>

> "Adrian Carlson-Hedges" <adrian.ch_at_btinternet.com> wrote in message

news:<a87mrh$oma$1_at_helle.btinternet.com>...


> > If I had to guess at the cause of the problem, it would be that you do

not


> > have uniform extent sizes, and hence you are fragmenting this tablespace

> > into many small chunks. Each time it needs to grab a decent sized chunk

it


> > has to grow the tablespace. If this is the problem, then using a uniform

> > extent size should cure this.

> >

> > As to how to go about reducing it. That would probably depend on your

> > circumstances. If you have only a few users, you could simply create a

new


> > Temporary tablespace, and assign this as their TEMP tablespace. I'm not

sure


> > when the assignment will happen. It probably won't affect connected

session,


> > and hence man not be until they next login. But at least in a few

hours/days


> > you should be able to drop (and if necessary) recreate your TEMP

tablespace.


> >