| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: syntax to remove TEMPORARY Tablespace?
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
> 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: 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
> 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: zhu chao INET: chao_ping_at_vip.163.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 Thu Sep 18 2003 - 13:54:43 CDT
 |
 |