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