On Wed, 7 Apr 1999, Hearley, Jennifer wrote:

> Hi Ari,
> I have a question that I hope you can answer. I have created a temporary
> tablespace that I have assigned to certain users who run jobs that require
> large table joins. My question is: does Oracle recycle the space once the
> job has finished running? We are pressed for space and that file usually
> seems pretty large. I was thinking of moving it to its own partition but I
> wanted to know what information you had if any.
> Thanks,
> Jenn Hearley

Yes, Oracle will recycle the space. What I mean is that Oracle frees
up the tablespace. To the operating system, the temp tablespace
datafile will always be there (Oracle pre-allocates the file before
using it, as it does with all tablespaces).

Here is a quick overview of the join process:

1) Oracle will try to do a join in the SORT_AREA (defined by
2) If the SORT_AREA gets filled up, Oracle writes temporary segments
   to the user's temporary tablespace.
3) Once the operation has completed, an Oracle background process will
   remove these temporary segments
4) Contiguous free space is NOT automatically coalesced unless the default
   PCTINCREASE storage parameter for the temporary tablespace is non-0.
   If you want to coalesce, issue:

   ALTER TABLESPACE temp_tablespace_name COALESCE;


-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 280+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page htm"> Back to Ari Kaplan's Home Page