Oracle-L: Re: How to drop a datafile from a tablespace quickly

From: Rodd Holman <roddholman_at_HotPOP.com>
Date: Wed, 15 May 2002 16:58:20 -0800
Message-ID: <F001.00461CF5.20020515165820@fatcity.com>

AFAIK you can't "quickly" drop a datafile. What we have done in this situation is to resize the datafile to 1 byte or however small you can make it. That way oracle can never write an extent there. Then do the export/drop tablespace/import routine at your next maintenance.

Rodd

On Wed, 2002-05-15 at 11:58, Xie, Tom wrote:

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: Rodd Holman
INET: roddholman_at_HotPOP.com

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