Re: TEMP tablespace should be temporary or permanent ?

It's a no-brainer. TEMP tablespace ought to be TEMPORARY. Without question, without thoughyt, and without argument.

In permanent tablespace, PGA swaps to disk, and extents are acquired, and then dropped when the sort has finished (lots of extent allocation/deallocation -ie, I/O on the system tablespace, and potentital for fragmentation).

In proper temporary tablespace, extents are allocated on the first sort and not deallocated until the datbase bounces. That means second and subsequent sorts don't have to allocate extents (they re-use previously-allocated ones) -which means they run faster, and since no extents are relinquished, there is absolutely no possibility of fragmentation.

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



<new_dba_at_my-deja.com> wrote in message news:8ppr45$p0j$1_at_nnrp1.deja.com...


> Hi,

>

> I am wondering whether TEMP tablespace should be temporary or

> permanent. What is the main difference ?

>

> The TEMP tablespace in our production database is permanent.  How do I

> decide whether I will benefit by making it temporary.

> Thanks.

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.