Re: temporary temp vs permanent temp

"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

Actually, Ghee, a true temporary tablespace is created using: create temporary tablespace temp
tempfile 'xxxx'

When you use "create tablespace temp temporary" you have told Oracle to store your temporary segments in a permanent tablespace but to allocate one temporary (sort) segment for the instance and to manage its contents locally rather than use the system ST lock to manage separate segments for every user which is what "create tablespace temp [permanent]" does by default.

Now if you are using a true temporary tablespace there have been a couple of bugs that have prevented Oracle from releasing the sort extents used by one session for reuse by other sessions and conversion back to 'temp temporary' could resolve them, but if you are type 'temp temporary' converting to 'temp permanent' is unlikely to fix your problem.

My best guess is you either need more sort space, larger extent sizes, or both in order to support your application. And yes Oracle sort-merge operations and hash joins use the same temporary segment(s) used to support sorting.

Here are two links to articles at the cooperative FAQ on the subject of temporary segments that may be of help:

http://www.jlcomp.demon.co.uk/faq/sort_user.html http://www.jlcomp.demon.co.uk/faq/fulltemp.html

• Mark D Powell --