Re: DBMS_JOBS and Temp Tablespace

"Anon" <cxy2002um_at_yahoo.com> wrote in message news:521a1a8f.0402201501.5302a8b8_at_posting.google.com...
> 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.

Absolutely the wrong way round. In proper temporary tablespace, temporary segments are only dropped when the instance is bounced. The whole point of temporary tablespace is that we *don't* want to keep dropping and re-creating stuff at a rate of knots. Instead, we simply re-use the space that a previous sort has already allocated.

Temporary tablespace should be 100% full under normal use.

> 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?

Nope. Shutdown and startup and then see what happens.

> Is there a way to determine the offending sql that using up temp
> tablespaces?

Yes. Have a look at v$sql to get explain plans that include sorts. Also have a look at v$sort_usage which shows you the sessions doing sorts (though being a v$, you have to query it as the sort is actually taking place, not ten minutes afterwards).

>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?

Because that's not how they are designed to work.

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------