The difference between a Contents of TEMPORARY and one of PERMANENT is
that you can create permanent objects (like tables or indexes) in a
tablespace that is considered PERMANENT and you cannot in one that is
considered TEMPORARY.
you need to do the following:
select segment_name, segment_type from dba_segments where
tablespace_name='TEMP';
and see if there are any permanent objects in the tablespace.
If there aren't any, you're lucky, this will be easy, all you have to
do is alter tablespace temp temporary;
this will change the tablespace to contents of temporary and no one can
create anything in it.
if there ARE permanent objects in the TEMP tablespace, it gets a little
trickier.
If you have enough disk space, the easiest thing to do is to create
another temporary tablespace as truly temporary and assign the users to
it. Then you can move the other objects out of TEMP slowly, and when
it's empty you can drop TEMP.
something like:
CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE 'file_1.f'
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
this creates a true tempfile, and nothing can be written in it. It also
takes advantage of locally managed tablespaces.
or you can create it as:
CREATE TABLESPACE tbs_1
DATAFILE 'diska:tabspace_file2.dat' SIZE 20M
DEFAULT STORAGE (INITIAL 10K NEXT 50K
MINEXTENTS 1 MAXEXTENTS 999) temporary
ONLINE;
this creates a tablespace with a datafile whose contents are TEMPORARY
whichever way you create the tablespace you would then need to issue
alter <user> temporary tablespace tbs_1;
and then move everything out of TEMP.
--- "Imma C. Rocco" <[EMAIL PROTECTED]> wrote:
> 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).
__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
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).
