-
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
"Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|