Re: Shrink Temporary Tablespace

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 09 Oct 2003 06:51:05 +1000
Message-Id: <3f84794a$0$7066$afc38c87@news.optusnet.com.au>

Charles J. Fisher 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?
>

Arrrrgh! When will people stop setting autoextend!!!!@!!!!!!

Then this mess wouldn't have happened in the first place. Autoextend is -or ought to be- the preserve of the incompetent, the lazy and the clueless (and I exaggerate only slightly), and SYSTEM.

As to the actual problem, a shutdown should cause SMON to de-allocate the temporary segment, and thus permit the shrink after you startup restrict (to prevent any users sneaking in there and starting to use the temporary tablespace, and thus allocating a new temporary segment).

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 - 15:51:05 CDT