Re: Shrink Temporary Tablespace

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 09 Oct 2003 07:35:30 +1000
Message-Id: <3f8483b3$0$7066$afc38c87@news.optusnet.com.au>

Turkbear wrote:

> "Charles J. Fisher" <cfisher_at_rhadmin.org> wrote:
> 

>>On Fri, 26 Sep 2003, Steven wrote:
>>
>>> One of my users ran an incorrect query and increased the temporary
>>> tablespace to it's maximum of 4GB. Until now it was static around
>>> 400MB. I want to shrink the temporary datafiles back to their normal
>>> size but I can't
>>> seem to do this. It's won't allow me to.
>>
>>You have probably got a sort segment allocated on this TEMP tablespace.
>>
>>IXORA says that you can drop this segment by respecifying PCTINCREASE:
>>
>>http://www.ixora.com.au/q+a/space.htm
>>
>>Only problem is that you are on an LMT, and this storage parameter won't
>>work.
>>
>>I wonder if there is a workaround?
>>
>>

> If it is a true TEMPORARY type tablespace  then is is still usable ( as
> you have probably found out by now ) even though it
> shows as full..I believe that if you bounce  the instance, it will revert
> to its initial size.

The datafile (or should i say the 'tempfile') will never revert to its initial size. At shutdown, the temporary segments are cleared, sure enough. But the physical storage used to house it doesn't magically shrink.

> 
> You can, I have read, alter the tablespace to permanent then back to
> temporary ( only do this if no active sorts are using the space)..

In the first place, you wouldn't be able to alter the tablespace if it was active. In the second, that advice is perfectly correct only for temporary tablespaces created in the 'old-fashioned' way (ie, create tablespace temp datafile X temporary). If the temporary tablespace is created in the 8i and upwards way (create temporary tablespace tempfile X), then you an't flip between temporary and permanent states.

If the guy is using LMTs for his temporary tablespace, then it rather suggests that he's using the tempfile variety (which must be LMT), and therefore he's not going to be able to alter the nature of the tablespace in that way.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Wed Oct 08 2003 - 16:35:30 CDT