Re: TEMP TABLESPACE PROBLEM

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Sat, 25 Oct 2003 18:12:34 GMT
Message-ID: <m2zmb.4329$4O1.2984@nwrdny01.gnilink.net>

"Paul" <paul821_at_yahoo.com> wrote in message news:DVymb.33897$gA1.11016277_at_news4.srv.hcvlny.cv.net... I have several databases running on Solaris 2.8 and Oracle 8.1.7.4. I only have this problem with one of them.

The temp tablespace always fills up and Oracle does not clear it out. If I expand the size of it, it still fills up. Even if all processes are finished, Oracle does not clear it out. Then I have programs fail with the "Unable to extend" error on the temp tablespace.

We either have to bounce the DB, or drop and re-build the temp tablespace when this happens.

Does anyone out there have any ideas why this happens in only one application that I support, and how to fix it ?

  Thank you.
  --
  Paul L.

How big is that tablespace?

You need to find out why that tablespace is filling up. Usual suspects would be SQL's doing a combination of sorting and cartesian join.

You can use v$sort_usage to check who is using the temp tablespace.

.. Also note: Temp tablespaces are not cleared up .. but recycled. So it is ok for the tablespace usage to be around 90-99%. SQL's failing from "unable to extent" is not good.

Anurag Received on Sat Oct 25 2003 - 13:12:34 CDT