DBMS_JOBS and Temp Tablespace

I have a problem with temp spaces filling up. Correct me if I wrong, but I believe I have read many times that temporary segments are dropped by Oracle when a transaction has completed or a session is closed.

Well, if I am doing some pretty good size batch processing every night scheduled using DBMS_JOBS, is each night its own session? So, if my batch process uses up 10 gigs of Temp tablespace, shouldn't that used space in temp tablespace be freed up at the end of the batch process?

Is there a way to determine the offending sql that using up temp tablespaces? I am running 9iR2 on a Windoze box with 2 Gig of ram. I am a bit baffled by the size of my temp tablespace but more so, why isn't it clearing out after each dbms_job run?

Thanks for your input. Received on Fri Feb 20 2004 - 17:01:03 CST