Re: Instead of restarting the instance...

Chris L. 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

See metalink Note: 1039341.6 and its references. Maybe the DBA needs to make the tablespaces PERMANENT so SMON cleans it up (or maybe he needs to make it much bigger), maybe he needs to check v$sort_segment and v$sort_usage to see what to kill.

Note also that if your new tool does a lot of DDL on the fly, that will be like your kittycat rolling the entire roll of toilet paper into your toilet. It won't flush well. For your toilet, you could switch how you load the paper, but for your temp segments, well, Sybrand gave some good advice - but see below about bugs.

Metalink Note: 47400.1 says how to set an event to drop segments, but I never had much luck with it, probably some locking issue I suppose.

There are bugs here and there, it might be worth it for you to do an advanced search of the bug database for ORA-1652. I thought not-a-bug# 4251058 was kinda interesting. Are you using DMT's? Bug 3922605 is especially interesting. I think the optimizer may yet have misfeatures. Horrors!

And of course, it could be completely different if you are on RAC.

>
> Thanks a lot
> Chris

jg

-- 
@home.com is bogus.
http://necrobones.com/neosci/products.html