Oracle-L: RE: How to drop a data file from a tablespace quickly

From: Sherman, Paul R. <PSherman_at_elcom.com>
Date: Wed, 15 May 2002 10:18:29 -0800
Message-ID: <F001.0046161B.20020515101829@fatcity.com>

We tried drop offline and it did not work. It appears that once the datafile is accessed in any way, you can not get rid of it by dropping it. If there is viable data, export it. Then drop the tablespace, rm the datafile(s) (UNIX), re-create the tablespace, and then import the export (assuming you did an export).

Thank you,

Paul Sherman
DBA Elcom, Inc.
email - psherman_at_elcom.com

-----Original Message-----
Sent: Wednesday, May 15, 2002 1:44 PM
To: Multiple recipients of list ORACLE-L

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

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------