Re: DBMS_JOBS and Temp Tablespace

Anon wrote:
> 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.

Most important problem first: the offending SQL. Check for unnecessary sorting. Knowing nothing about your batch process, I can only recommend first looking for a common developer mistake such as using "SELECT DISTINCT..." to work-around a poorly designed join which includes a cartesian product.

AFAIK, sort segments in a temporary tablespace (tempfiles) are not dropped, but are re-used. Are you saying that each job run is increasing the size (usage) of your temporary tablespace by an additional 10 GB? Or just that you can't shrink the tempfiles back down to a smaller size after the batch job?

--Mark Bole Received on Fri Feb 20 2004 - 18:02:34 CST