RE: How to drop a datafile from a tablespace quickly

Rachel,

you said
"It's not just grants. Any procedure that references that table will have to be
recreated, oracle uses object_id not object_name so the procedure will point to
the old table etc etc"

Can't he just re-compile the procedures? He doesn't have to re-create them.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
[mailto:Rachel_Carmichael_at_Sonymusic.com] Sent: Wednesday, May 15, 2002 3:19 PM
To: Multiple recipients of list ORACLE-L

It's not just grants. Any procedure that references that table will have to be
recreated, oracle uses object_id not object_name so the procedure will point to
the old table etc etc

you CAN try to resize the datafile down to something really small, smaller than
the smallest extent if possible, That will keep data out of it but you are going
to need downtime to fix this

|--------+----------------------->


|        |                       |

|        |                       |

|        |          dgoulet_at_vicr.|

|        |          com          |

|        |                       |

|        |          05/15/2002   |

|        |          01:48 PM     |

|        |          Please       |

|        |          respond to   |

|        |          ORACLE-L     |

|        |                       |

|--------+----------------------->
  >----------------------------------------------------|
  |                                                    |
  |       To:     ORACLE-L_at_fatcity.com                 |
  |       cc:     (bcc: Rachel Carmichael)             |
  |       Subject:     Re:How to drop a datafile from a|
  |       tablespace quickly                           |
  >----------------------------------------------------|

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: 
  INET: Rachel_Carmichael_at_Sonymusic.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).