Re: Temporary tablespace near 100% full, smon is not freeing up, Oracle 7.3.4.5 ----> URGENT

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:vEjT9.20020$jM5.54351_at_newsfeeds.bigpond.com...
> Temporary tablespace is *supposed* to be 100% full.
>
> When permanent tablespace is used for user sorts, then PGA-sized extents
are
> allocated within it, only to be dropped when that user's report finishes.
> The next sort to disk then has to go to the bother of re-allocating the
> extents.
>
> When proper temporary tablespace is used for sorts, the extents are
> allocated as before by the first person doing a sort. At the end of his or
> her report, the extents he caused to be allocated are *not* dropped, but
are
> merely marked for re-use by other users. This makes the second and
> subsequent sorts run faster than they would have done in permanent
> tablespace.
>
> But it also means that the space in the temporary tablespace is not
> released. Eventually, the entire temporary tablespace will have extents
> marked out within it, and it will look 100% full. But that doesn't stop
> other people from re-using those extents, and it isn't a problem. In fact,
> it's a design feature. Indeed, if your temporary tablespace is only (say)
> 94% full, then it means you are wasting 6% of disk space.
>
> Rather than worrying about how full the temporary tablespace is, you
should
> only be concerned if users report that their sorts are failing for lack of
> space. And if that's the case, the issue is merely that your temporary
> tablespace is too small.
>
> With all that said, you can prod SMON to do its stuff if you are a bit of
a
> masochist by merely altering the tablespace to permanent, and then making
it
> temporary again:
>
> alter tablespace TEMP permanent;
> alter tablespace TEMP temporary;
>
> Regards
> HJR
>

Howard, this hits a spot.

Once upon a time (about 6 months ago IIRC) we had a fellow DBA in the office who was, shall we say, somewhat set in his ways. One day he looked at a (8.1.6) db of mine, and raised a panic because the TEMP tablespace was 100% full.
I told him that this wasn't a problem, but he persisted. He had _never_ seen anything like this (because, refusing to believe that anything was new since Oracle 6, he had never specified his TEMP tablespaces as TEMPORARY, let alone use 8i tempfiles). I started to explain that since Oracle 7, the effect of making a tablespace TEMPORARY was to make the temporary segments permanent (i.e. they don't get cleaned up but are available for reuse), but I could see his eyes glazing over and a bout of bluster about to start....

"I'll check it out" quoth I, to avoid further argument.

Then, while no-one was looking, (and **exactly** as you specify):

alter tablespace TEMP permanent;
and after a few seconds:
alter tablespace TEMP temporary;

What a waste of time and resources! But it kept the peace....

Regards,
Paul Received on Fri Jan 10 2003 - 14:35:37 CST