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).
-- login as sys, ran 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: [EMAIL PROTECTED] 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: [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.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [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).
