Re: About Temporary tablespaces and temporary segments allocation

Imma,

A database when created, by default, has all tablespaces as of type PERMANENT.
A sort which exceeds the sort_area_size allocated in memory goes to the user's TEMPORARY_TABLESPACE
which, btw, CAN be a PERMANENT tablespace. Thus, sorts would allocate Temporary Segments
in the tablespace named TEMP.
The issue with having a PERMANENT TEMPORARY_TABLESPACE is that you would have
frequent segments being created and dropped and extents being allocated and dropped within
each segment whenever users do large sorts. This could be a performance hit and can fragment
the Tablespace.
If there are no segments in the tablespace, you could just do an ALTER TABLESPACE temp TEMPORARY
and a single sort segment would be created on the first sort/usage of the tablespace.
Note that once the tablespace is TEMPORARY, you cannot create any other objects (Tables/Indexes)
in it. You can segments and extents in PERMANENT tablespaces in DBA_SEGMENTS
and DBA_EXTENTS. For TEMPORARY tablespaces, query V$SORT_SEGMENT and V$SORT_USAGE.

If you do have other segments already present in the tablespace, you would have to
move them out (Export-Drop-Create_in_new_TBS-Import OR Copy-Drop-Rename) OR
create another tablespace of type TEMPORARY and set that as the user's temporary tablespace
CREATE TABLESPACE temp2 datafile 'adfa' TEMPORARY; ALTER USER <username> TEMPORARY TABLESPACE temp2;

Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd

"Imma C. Rocco" <imma_at_lycos.com> 01/02/2002 04:35 AM Sent by: root_at_fatcity.com

Please respond to ORACLE-L

                                                                                       
                        
             To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>           
                        
             cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)                     
                        
             Subject: About Temporary tablespaces and temporary segments allocation    
                        
                                                                                       
                        
                                                                                       
                        
                                                                                       
                        

Hi,
I have probably to beg you pardon for my question but I have a very short experince as an Oracle dba and it is the first time I found a situation like the one I'm going to describe to you.

Working on an existing Oracle database I found that all users had been defined with an associated temporary tablespace named TEMP but selecting from dba_tablespaces the TEMP tablespace resulted to be PERMANET not TEMPORARY - problably because it had been turned from TEMPORARY to PERMANENT in a later time.

I would like to know what happens in case the SORT_AREA_SIZE in not large enough to manage with sort opererations - are temporary segments still allocated on the TEMP tablespace (despite the fact that it is not TEMPORARY but PERMANET) or a temporary segment could be allocated only on a temporary tablespace?

Thanks in advance
Imma

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Imma  C. Rocco
  INET: imma_at_lycos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: hemantchitale_at_charteredsemi.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).