Re: About Temporary tablespaces and temporary segments allocation

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Thu, 31 Jan 2002 18:59:41 -0800
Message-ID: <F001.00402D00.20020131184519@fatcity.com>

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" <imma_at_lycos.com> 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: 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).

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: wisernet100_at_yahoo.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).

Received on Thu Jan 31 2002 - 20:59:41 CST