Re: DBMS_JOBS and Temp Tablespace

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40369923$0$15138$afc38c87_at_news.optusnet.com.au>...
> "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
I know you are correct, because this is what I am observing in my database.

But if you look at the following link:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c03block.htm#2922 or
http://tinyurl.com/395vc

The last line under Extents in Tempoary Segments. "Oracle drops the temporary segments......."

Okay, I must be confusing temporary segments with sort segments. Received on Mon Feb 23 2004 - 10:01:33 CST