Instead of restarting the instance...

From: Chris L. <diversos_at_uol.com.ar>
Date: 10 Jun 2005 15:13:30 -0700
Message-ID: <1118441610.732358.275730@g14g2000cwa.googlegroups.com>

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

Thanks a lot
Chris Received on Fri Jun 10 2005 - 17:13:30 CDT