Re: temporary temp vs permanent temp

From: Hemant K Chitale <hkchital_nospam_at_singnet_nospam.com.sg>
Date: Mon, 21 Jan 2002 21:19:45 +0800
Message-ID: <a2h7sh$pda$1@dahlia.singnet.com.sg>

You can check the status of the Temporary Segment (a Tablespace which is Temporary has only one Temporary Segment) by querying V$SORT_SEGMENT. Do an ALTER TABLESPACE <tablespace_name> DEFAULT STORAGE (PCTINCREASE 0); -- this should "clear" the temporary segment if it is not in use at that time (check V$SORT_SEGMENT.ACTIVE_USERS).

Hemant K Chitale

"G.Ong" <onggs_at_acslink.aone.net.au> wrote in message news:FPS28.6783$N31.292102_at_ozemail.com.au...
> Hi,
>
> Our system is 8.1.6.3 on Solaris 2.7.
>
> We used to used to have temporary segments on temporary tablespace (ie
those
> created : create tablespace xxx temporary ). However, because of a number
of
> unable to extend temporary segments, the system DBA recommend using
> temporary segments on permanent tablespace (because the sort segments are
> immediately released).
>
> Questions:
> 1) Is this a valid decision ?
> 2) If a hash join occurred involving a large table, does it use the same
> sort segment created for sorts ? (In this case, assuming a temporary temp
is
> used.)
>
> TIA,
> Ghee
>
>
Received on Mon Jan 21 2002 - 07:19:45 CST