Re: Are temp segments being freed automacically or not ?

TEMP segments will be freed, eventually and automatically.

Sometimes, a user requests a sort and needs TEMP space to facilitate that sort. So a TEMP segment is allocated by the system. The user finishes with the sort and no longer needs the TEMP space. The system could automatically free this temp segment at that time. But what if another user needs a temp segment? Why not keep around the first temp segment for a while to see if another user will need it? That way, the system doesn't have to allocate something is just deallocated. That's the typical behaviour of TEMP segments in Oracle.

HTH,
Brian

Jan Gelbrich wrote:
>
> Hello,
>
> I would like to know if my observation is something to worry about or not
> ...
>
> My system:
> Oracle EE 8.1.7 on AIX4.3.3, 25 GB of data, 200 sessions,
> dedicated server config, closed inhouse app based on Forms.
>
> Some months ago we rebounced the instance with a TEMPORARY tablespace
> (just as things should be - and should have been in the first place ...),
> and we saw about 54% usage - OK. Over weeks and weeks, there was no
> recognizable change
> to this figure, so we thought, well it seemed to have been right
> dimensioned.
>
> Since yesterday we saw 80% and began to wonder ...
>
> Ok, tablespace increasing is not the issue here,
> I just rememebered that in some manuals, also in Guy Harrisons tuning book,
> it says that temporary segments (hold by SYS) are allocated automatically
> by the instance, *and* deallocated the same way.
>
> But what I see seems to be a HWM of the segment that never decreases.
> One thing is to add: the TS is still a DMT ... but would a LMT behave so
> much different ?
>
> So, in the end: how do temporary tablespaces behave ?
>
> Any tips would be greatly appreciated
> (besides RTFM, I do that everyday)
>
> ThanX in advance,
> Jan

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"