Oracle TEMP tablespace grows to 30GB.

Hi Everyone,

I would like anyone to share his experience with oracle9 with the temporary TEMP tablespace in AUTOEXTEND mode.

I have it on a Windows 2003 Standard Edition, oracle patched to 9.2.0.8. Before I had a problem with the temporary TEMP tablespace, with not a very large query the tempfile could sometimes grow to 30GB. On a daily basis a 500MB file was enough. I ran onto an Oracle bug - problem with extending beyond a 4GB limit. This was supposed to be fixed with the patch I applied (as Oracle asked me), but my problem continues to persist.

I set up an SQL to check that is on my TEMP - sorts at that time. Can anyone suggest, if I am doing it right:

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') SDATE,
opname,
target,
TARGET_DESC,
SOFAR/1024 SOFAR_KB,
TOTALWORK/1024 TOTALW_KB,
to_char(START_TIME,'YYYYMMDD HH24:MI:SS') START_TIME,
to_char(LAST_UPDATE_TIME,'YYYYMMDD HH24:MI:SS') LAST_UPDATE_TIME,
ELAPSED_SECONDS,
MESSAGE,
USERNAME
from V$session_longops
/

This leads me to a result showing I am using only about 250MB of space. maybe there is another way so see how much space I really need? The same query runs every day on the same amount of data at 10PM. When I rebuild the TEMP tablespace, everything is processed with any problems.

Has anyone had this kind of a problem?

Regards,
Richard

limit the max file size..

Bear in mind how the TEMP tablespace works - if you let it grow then it will, if you don't then it will reuse no longer needed space. With autoextend on it will continue to allocate space, but if you turn autoextend off then when it reaches full it will start re-using previously allocated space (if that makes sense!).

Quote:

---

Originally Posted by cis_groupie

Bear in mind how the TEMP tablespace works - if you let it grow then it will, if you don't then it will reuse no longer needed space. With autoextend on it will continue to allocate space, but if you turn autoextend off then when it reaches full it will start re-using previously allocated space (if that makes sense!).

---

Hi,

thanks, is it written somewhere in the documentation? I'll take a look, it's seems strange to me the space is no longer reused once released when working in autoextend mode, it would be rather silly. I am just used to traditional Oracle7/8 temp tablespaces. But this could explain why I am running into this. Also the database is closed each night during a backup. Everything works fine for a few days, the temp file does not even grow beyond 1Gb and then suddenly one day it bursts into the OS limit which is 30GB. I had a feeling this is still related to the 4GB Oracle bug.

I will try the option without the autoextend off.

Many thanks,
Richard.

If you are closing the Db every night then the TEMP tablespace will clear itself (although it's still a good idea to turn autoextend off).

If the TEMP space is OK for a few days & then suddenly grows very fast I would be tempted to see what's running - it sounds like a very inefficient bit of code is suddenly staring, needing lots of temp space (maybe a cartesian join?).

Quote:

---

Originally Posted by cis_groupie

If you are closing the Db every night then the TEMP tablespace will clear itself (although it's still a good idea to turn autoextend off).

If the TEMP space is OK for a few days & then suddenly grows very fast I would be tempted to see what's running - it sounds like a very inefficient bit of code is suddenly staring, needing lots of temp space (maybe a cartesian join?).

---

Well, thanks fot the hint, just before going home I checked my procedure that collects the statistics - guess what? It was working fine for 2 months and just 3 days ago it started failing....you never know.
Just one thing to remember to check ....I'll see tomorrow if this is the reason.

Regards,
Richard.

any update on this?

Quote:

---

Originally Posted by ndisang

any update on this?

---

Hi,

well, my system works fine now. The problem is with the application/database, for 3 or 4 days I did not have the statistics collected. Simple.... This system is very sensitive for lack of statistics.

In fact it's the Windows Scheduler that screwed up, because instead of starting at 7PM it started at 4AM when my database was shutdown. From that point the trouble began.

I thank you all for your attention,
Regards,
Richard.