| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to drop a datafile from a tablespace quickly
no you can't recompile them... I tried that once, the recompiled procedure referenced the renamed (old) table
it's the object_id thing. Oracle doesn't care what you name or rename an object, it tracks it by the object_id.
Trust me, it screwed up triggers etc. real pita
|--------+------------------------>
| | |
| | |
| | NDATFM_at_labor.s|
| | tate.ny.us |
| | |
| | 05/15/2002 |
| | 03:53 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 | >----------------------------------------------------|
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). -- 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).Received on Wed May 15 2002 - 15:28:49 CDT
 |
 |