Re: Out of control TEMP tablespace

On Feb 20, 6: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

Others have already given you a solution for a.

As far as b. goes. I don't believe there is a way to determine what caused the issue in the past, unless you have awr reports or snap shots that were taken. Then it would still be hard to pinpoint the exact cause. Going forward though, you could setup a job to poll the temp space usage and keep track of the space that is being used. If it is a consistent problem it should come back up. If it was just some rogue ad-hoc sql then you may never see it again. Received on Wed Feb 20 2008 - 12:20:24 CST