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

From: Kimberly Smith <ksmith2_at_myfirstlink.net>
Date: Wed, 15 May 2002 17:58:32 -0800
Message-ID: <F001.00461D7F.20020515175832@fatcity.com>

Pretty sure that ain't a Oracle7 feature....

-----Original Message-----

Thomas F
Sent: Wednesday, May 15, 2002 12:43 PM
To: Multiple recipients of list ORACLE-L

Tom,

Or,

- create a new tablespace
- ALTER TABLE {table_name} MOVE {new_tablespace)
- alter index {all indexes that belong to this table) rebuild

Hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

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

Tom,

Regrettably there is no way that you can drop a datafile from a tablespace.
Your stuck with having to drop the entire tablespace. If you can export and import that's great. Otherwise try creating a new tablespace, copying the table
(with a new name) into the new tablespace, dropping the original table and tablespace, and then rename the table back to it's old name. Don't forget the
grants as well.

Dick Goulet

____________________Reply Separator____________________
Author: "Xie; Tom" <TXie_at_taylorpub.com>
Date:       5/15/2002 8:58 AM

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:
INET: dgoulet_at_vicr.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

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kimberly Smith
INET: ksmith2_at_myfirstlink.net

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