TEMP Tablespace Question

[Paul]

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.

[Anna C. Dent]

Re: TEMP Tablespace Question

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.

EXACTLY which type of "temp" tablespace do you have;
permanent or temporary?

Have you tried ?
ALTER TABLESPACE TEMP COALESCE;

[Paul]

Re: TEMP Tablespace Question

Anna:

It is a temporary tablespace. I haven't tried the coalesce but I will
give it a try.

Thanks.
--
Paul
"Anna C. Dent" <anacdent@hotma il.comwrote in message
news:BnCmb.8488 8$Ms2.27047@fed 1read03...

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.

>
EXACTLY which type of "temp" tablespace do you have;
permanent or temporary?
>
Have you tried ?
ALTER TABLESPACE TEMP COALESCE;
>

[Frans Hinlopen]

Re: TEMP Tablespace Question

Paul,

This is a golden oldie in Oracle - just do a Google search. As I understand
it as a developer, it is not unusual for TEMP to be running at 99% usage,
since the space is usually reclaimed on demand or through a process that
wakes up every hour or so.

Given the circumstance that it only happens in one app, I expect it to be a
suboptimal query that is doing a superfluous join/sort whatever. It happened
to me once too:
Me: "My program sometimes crashes with ORA-01652 ".
DBA: "There's something wrong with your program".
Me (arrogantly): "Writing optimal and correct Oracle SQL queries has become
second nature to me".
DBA: "There's still something wrong with your program".
Me (after some research, shamefaced): "I found a non-optimal query in my
program that caused a Cartesian join".

Just use a tool like PL/SQL Developer or TOAD or one of the others to easily
explain plan your queries.

Regards,
Frans