Re: Instead of restarting the instance...

On 2005-06-10, Chris L. <diversos_at_uol.com.ar> wrote:
> Hi all,
>
> The Problem: We're getting lots of "ORA-01652: unable to extend temp
> segment by 128 in tablespace TEMP1" messages. The DBA enlarged the
> TEMP1 segment (added three 1GB tempfiles to it) AND suggested to
> "restart the instance once a day" to have better results.
>
> What could be done to avoid this restarting? I've read
>
> http://www.jlcomp.demon.co.uk/smon.doc
>
> and
>
> http://www.jlcomp.demon.co.uk/faq/fulltemp.html
>
> This doc seems to suggest that "The segment and pool of extents [are]
> released only when the instance [is] shut down, and [are] cleaned up on
> the next startup."
>
> Can't anything be done except restart the instance daily? (up to now
> it's been having a weekly restart and it's been that way for about 4
> years)
>
> If you're wondering what has changed to make it stop working, the user
> has installed a new reporting tool (part of it is web-based) that seems
> to use lots of TEMP space when querying the base. Using Oracle 9i
> 9.2.0.4.0

While it is correct that temp segments are only freed when the DB is restarted, it does not mean that Oracle cannot re-use temp segments that are not used anymore. With other words, you have to expect that the temp usage approaches 99.9% for a long running instance. This is by design and should not concern you.
However, if you repeadetly run into an ORA-01652 you should, as suggested by Sybrand, change the offending SQL Statements that they do not exceed the available temp space.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/