468,107 Members | 1,343 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

TEMP Tablespace Question

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.
Jul 19 '05 #1
3 15878
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;

Jul 19 '05 #2
Anna:

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

Thanks.
--
Paul
"Anna C. Dent" <an******@hotmail.com> wrote in message
news:BnCmb.84888$Ms2.27047@fed1read03...
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;

Jul 19 '05 #3
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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by hank | last post: by
reply views Thread by sethwai | last post: by
1 post views Thread by airmax | last post: by
9 posts views Thread by Veeru71 | last post: by
3 posts views Thread by Paul | last post: by
3 posts views Thread by Arun Srinivasan | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.