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.

"Paul" <paul821 (AT) yahoo (DOT) com> wrote

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

Paul wrote:


I've read Anurag's response and want to take an entirely different approach.

First ... what's the problem? In other words ... why do you care? Is it
because
you are getting error messages? If so post them. Or is it because you
are looking
in some data dictionary view and think it is supposed to show as empty
.... in
which case you should immediately stop trying to be a DBA and start reading
the manuals on how the temp tablespace works.

From what you posted it is impossible to tell.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Thanks Daniel:

You have been a big help.
--
Paul
"Daniel Morgan" <damorgan@x.washington.edu> wrote

Paul wrote:

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.

I've read Anurag's response and want to take an entirely different approach.

First ... what's the problem? In other words ... why do you care? Is it because
you are getting error messages? If so post them. Or is it because you are looking
in some data dictionary view and think it is supposed to show as empty .... in
which case you should immediately stop trying to be a DBA and start reading
the manuals on how the temp tablespace works.

From what you posted it is impossible to tell.


--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)