Re: syntax to remove TEMPORARY Tablespace?

You cant take a "temporary lmt" temporary tablespace offline, just drop it. The drop command will wait until there is no needed temp data left.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, September 17, 2003 9:44 PM

> 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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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