Re: How to drop a data file from a tablespace quickly

Uh-oh... another urban legend?

ALTER DATABASE DATAFILE '...' OFFLINE DROP is not a command to drop a datafile from the tablespace.

The file is be in the v$datafile and controlfile FOREVER even you issue the offline drop command.

That command is only for you to put that datafile in the RECOVERY mode and tell Oracle that you will recovery that file later.

You will HAVE TO DROP THAT TABLESPACE if you cannot recover that file.

"Mandal, Ashoke" wrote:
>
> ALTER DATABASE <database_name> DATAFILE '<filename>' OFFLINE DROP;
> Then remove the data file physically from the directory in UNIX or NT or any other server.
>
> Thanks,
> Ashoke
> -----Original Message-----
> Sent: Wednesday, May 15, 2002 11:58 AM
> To: Multiple recipients of list ORACLE-L
>
> Dear gurus:
>
> I just added a data file to a big tablespace (11GB) that has only one
> table. Unfortunately, when it was being backed up, the file head head
> corrupted. I don't have any backup of this file. I found that there is
> no data in this file yet. So I want to drop the file from the
> tablespace. As I know, to drop a data file, I have to export the data,
> drop the tablespace and recreate it, and then import data back to
> the tablespace. However, since our users can't stop using the table,
> I won't have enough down time to do that.
>
> Is there anyway I can quickly drop a data file from a tablespace?
>
> Don't tell me using "alter database datafile '...' offline drop" command.
> It won't work.
>
> I am working on Oracle 7.3.4.
>
> Thanks,
>
> Tom Xie
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Xie, Tom
> INET: TXie_at_taylorpub.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
> --
> Author: Mandal, Ashoke
> INET: ashoke.k.mandal_at_medtronic.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Suzy Vordos
  INET: lvordos_at_qwest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).