Hi, guang:
Another possible solution is like, just similiar. You can individually drop
tempfile from temporary tablespaces, not like datafiles.
SQL> create temporary tablespace tmptest tempfile '/home/oracle/tmp1.tmp' size 30m;
Tablespace created.
SQL> alter tablespace tmptest add tempfile '/home/oracle/tmp2.tmp' size 30m;
Tablespace altered.
SQL> alter database tempfile '/home/oracle/tmp1.tmp' drop;
Database altered.
SQL> alter database tempfile '/home/oracle/tmp2.tmp' drop;
Database altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
DATA
RBS
TEMP
TMPTEST
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 18, 2003 4:04 AM
> 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).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: zhu chao
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).
