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
Oracle TEMP tablespace grows to 30GB.
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.