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" <[EMAIL PROTECTED]> 01/02/2002 04:35 AM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
