Re: Shrink Temporary Tablespace

"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?
>
> ---------------------------------------------------------------------------
> / Charles J. Fisher |"The prettiest girls in the world /
> / cfisher_at_rhadmin.org | live in Des Moines, Iowa." /
> / http://rhadmin.org | -Jack Kerouac /
>---------------------------------------------------------------------------
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.

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).. Received on Wed Oct 08 2003 - 15:58:27 CDT