Re: Out of control TEMP tablespace

On Feb 20, 7:15 am, trub3101 <trub3..._at_sky.com> wrote:
> On Feb 20, 11:54 am, trub3101 <trub3..._at_sky.com> wrote:
>
>
> > Hi Gurus,
>
> > Unfortunately, in our LIVE database the TEMP tablespace is set as
> > UNLIMITED for max size with autoextend on.
> > The tablespace was originally sized for 1000 MB but in the space of a
> > few days has grown to over 25000 MB as a result of this my filesystem
> > where this temp file is based is creaking!
>
> > A couple of things;
>
> > a) How can I reset the TEMP tablespace back so that it only grows to a
> > more manageable size without bouncing the database?
>
> > b) How do I decipher what caused this considerable growth in such a
> > short space of time?
>
> > Cheers,
>
> > tb3101
>
> Okay. Just as I thought I will either need to create a new default
> temporary tablespace and drop the old one at which point I can either
> rebuild the old one and make it the default again or just leave the
> default as the new one.
>
> Or I can just drop the tempfile for the temp tablespace and create
> another one (9i and higher!)
>
> Either way it suggests that noone should be using the database when I
> am doing this. Bah!
>
> Although, in the first scenario this seems somewhat precautionary as a
> default temp tablespace will always be present!
>
> tb3101- Hide quoted text -
>
> - Show quoted text -

Follow the plan where you create a new temporary tablespace, alter everyone to use it, wait a while, drop then recreate the original temporary tablespace, alter everyone back, wait a while, the offline and drop the replacement temp tablespace.

Check every datafile in your database to see that if it is set to be extendable that maximum size is set to a reasonable value.

HTH -- Mark D Powell -- Received on Wed Feb 20 2008 - 08:48:56 CST