syntax to remove TEMPORARY Tablespace?

From: Guang Mei <gmei_at_incyte.com>
Date: Wed, 17 Sep 2003 10:44:52 -0800
Message-ID: <F001.005D03E2.20030917104452@fatcity.com>

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). Received on Wed Sep 17 2003 - 13:44:52 CDT