Oracle-L: RE: syntax to remove TEMPORARY Tablespace?

From: Guang Mei <gmei_at_incyte.com>
Date: Wed, 17 Sep 2003 12:04:41 -0800
Message-ID: <F001.005D03F6.20030917120441@fatcity.com>

Never mind, I found the answer. For those who are interested, here is what I did (tested on dev first, then ran on production against 8173 db).

select count(*) from dba_segments where TABLESPACE_NAME ='TEMPLMT';

got 0 returned, then ran

ALTER DATABASE TEMPFILE '/oracle/u3/oradata/YPD/templmt01.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/oracle/u3/oradata/YPD/templmt02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/oracle/u3/oradata/YPD/templmt03.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/oracle/u3/oradata/YPD/templmt04.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/oracle/u3/oradata/YPD/templmt05.dbf' OFFLINE;

at this point, if I query dba_tablespaces, TEMPLMT's status was still "ONLINE", I just ran

drop tablespace TEMPLMT;

And then removed the datafiles from OS. Everything seems OK.

BTW, Oracle Doc does not seem to give out clear instruction of how to do this.

Guang

-----Original Message-----

Guang Mei
Sent: Wednesday, September 17, 2003 2:45 PM To: Multiple recipients of list ORACLE-L

Hi:

I did RTFM, but I could not find any answer.

I created two TEMPORARY Tablespaces (see below) and I moved all users to TEMP. Now I want to drop TEMPLMT. When I tried "alter tablespace TEMPLMT offline;" I got

alter tablespace TEMPLMT offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

I usually make the DMT offline, drop the ts, then remove the datafile(s) from OS. But the syntax does not seem to work here. Sp for those of you who have done before, what is the proper steps and syntax to drop a TEMPORARY Tablespaces (LMT)?

TIA. Guang

script: CREATE TEMPORARY TABLESPACE TEMPLMT TEMPFILE '/oracle/u3/oradata/YPD/templmt01.dbf' SIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

ALTER TABLESPACE TEMPLMT
ADD TEMPFILE '/oracle/u3/oradata/YPD/templmt02.dbf' SIZE 2000M;

ALTER TABLESPACE TEMPLMT
ADD TEMPFILE '/oracle/u3/oradata/YPD/templmt03.dbf' SIZE 2000M;

ALTER TABLESPACE TEMPLMT
ADD TEMPFILE '/oracle/u3/oradata/YPD/templmt04.dbf' SIZE 2000M;

ALTER TABLESPACE TEMPLMT
ADD TEMPFILE '/oracle/u3/oradata/YPD/templmt05.dbf' SIZE 2000M;

CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/oracle/u4/oradata/YPD/temp01.dbf' SIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 2M; ALTER TABLESPACE TEMP
ADD TEMPFILE '/oracle/u4/oradata/YPD/temp02.dbf' SIZE 2000M;

ALTER TABLESPACE TEMP
ADD TEMPFILE '/oracle/u4/oradata/YPD/temp03.dbf' SIZE 2000M;

ALTER TABLESPACE TEMP
ADD TEMPFILE '/oracle/u4/oradata/YPD/temp04.dbf' SIZE 2000M;

ALTER TABLESPACE TEMP
ADD TEMPFILE '/oracle/u4/oradata/YPD/temp05.dbf' SIZE 2000M;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Guang Mei
INET: gmei_at_incyte.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

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.net
--

Author: Guang Mei
INET: gmei_at_incyte.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message