Oracle TEMP tablespace grows to 30GB.
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 22.214.171.124. 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,
to_char(START_TIME,'YYYYMMDD HH24:MI:SS') START_TIME,
to_char(LAST_UPDATE_TIME,'YYYYMMDD HH24:MI:SS') LAST_UPDATE_TIME,
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?
"Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."
Click Here to Expand Forum to Full Width