Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to drop a datafile from a tablespace quickly

RE: How to drop a datafile from a tablespace quickly

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 15 May 2002 11:43:27 -0800
Message-ID: <F001.0046182A.20020515114327@fatcity.com>


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 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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us
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). Received on Wed May 15 2002 - 14:43:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US