RE: RE: How to drop a datafile ?
Prem I will provide my guesses and maybe someone that knows the real story will reply. First, in order for Oracle to drop a datafile, you would somehow have to ensure that it is empty. I would guess if Oracle added this feature, then Oracle Support would receive a lot of questions about why it erred out for them. Second, for any software system, what is the portion most likely to cause an error? The part that changed last. If they created this feature at this late date, it would probably produce weird behavior sometime. Finally, developers develop what they are requested to develop. Those requests generally come from marketing. For odd features like this marketing probably gets a list from support. So, if many customers aren't requesting a feature, it will probably never get on the development feature list. But that is all just a guess based on my personal experience working for a software vendor long ago on a faraway planet, and that vendor certainly wasn't Oracle. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 30, 2003 8:24 PM To: Multiple recipients of list ORACLE-L Thanx everybody. i thought there would be a way to do it. just curious . why is it not so ? any good reason for it ?!!! Regards, Jp. 30-10-2003 02:34:26, [EMAIL PROTECTED] wrote: That won't actually do what you're implying. Checking the docs would prove helpful. From the SQL ref: If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile offline. However, the DROP clause does not remove the datafile from the database. To do that, you must drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE. If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: RE: How to drop a datafile ?
Thanx everybody. i thought there would be a way to do it. just curious . why is it not so ? any good reason for it ?!!! Regards, Jp. 30-10-2003 02:34:26, [EMAIL PROTECTED] wrote: That won't actually do what you're implying. Checking the docs would prove helpful. From the SQL ref: If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile offline. However, the DROP clause does not remove the datafile from the database. To do that, you must drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE. If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
I don't beelive you can drop a datafile from a tablespace, you have to drop the tablespace, just reaize the datafile to 1M if space is the problem Regards From: Prem Khanna J [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How to drop a datafile ? Date: Tue, 28 Oct 2003 22:19:24 -0800 doc 111316.1 doesn't mention about using OEM for the same. so i hope it cannot be done with OEM. am i right LIST ? so how do i do it from sqlplus ? TIA. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: RE: How to drop a datafile ?
Thanx craig. but is there no way of dropping a datafile then ? Regards, Jp. 29-10-2003 18:24:25, Craig Richards wrote: I don't beelive you can drop a datafile from a tablespace, you have to drop the tablespace, just reaize the datafile to 1M if space is the problem Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
Hi, How about do these: 1. Create new tablespace 2. Move all objects from Old tablespace to new tablespace 3. drop old tablespace and delete the files 4. recreate the old tablespace with correct datafiles size 5. move all objects from new tablespace to redesign old tablespace 6. drop new tablespace and delete the files. Only if your database size is small or medium. Sinardy -Original Message- Sent: 29 October 2003 17:24 To: Multiple recipients of list ORACLE-L I don't beelive you can drop a datafile from a tablespace, you have to drop the tablespace, just reaize the datafile to 1M if space is the problem Regards From: Prem Khanna J [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How to drop a datafile ? Date: Tue, 28 Oct 2003 22:19:24 -0800 doc 111316.1 doesn't mention about using OEM for the same. so i hope it cannot be done with OEM. am i right LIST ? so how do i do it from sqlplus ? TIA. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How to drop a datafile ?
The doc is right. You cannot drop a single datafile from a tablespace. --- Prem Khanna J [EMAIL PROTECTED] wrote: Guys, it's oracle 9.2.0.3/Win2K. say for some reason (NOT recovery): i want to drop a datafile from a tablespace which has more than 1 datafile.how to do it ? Doc 111316.1 says u need to drop the tablespace or exp/imp the objects in the tablespace. your ideas please. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How to drop a datafile ?
Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How to drop a datafile ?
export the data (make sure you get all the data) drop the tablespace and recreate it import the data --- Prem Khanna J [EMAIL PROTECTED] wrote: Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
Well, you an actually move all your objects from this datafile into a different datafile (read it as different TS) and then offline drop the datafile. This will ensure that users don't get the error 'xxx.dbf file is currently inacessible' message. Cheers! Venu -Original Message- Prem Khanna J Sent: Wednesday, October 29, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Re: How to drop a datafile ?
if you have a tablespace with 10 datafiles. can you drop just one datafile? From: Rachel Carmichael [EMAIL PROTECTED] Date: 2003/10/29 Wed AM 08:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: How to drop a datafile ? export the data (make sure you get all the data) drop the tablespace and recreate it import the data --- Prem Khanna J [EMAIL PROTECTED] wrote: Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Re: How to drop a datafile ?
disregard my question. i saw it in an earlier post. sorry i have several hundred emails this morning. i missed it. From: Rachel Carmichael [EMAIL PROTECTED] Date: 2003/10/29 Wed AM 08:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: How to drop a datafile ? export the data (make sure you get all the data) drop the tablespace and recreate it import the data --- Prem Khanna J [EMAIL PROTECTED] wrote: Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Re: How to drop a datafile ?
Just to reiterate what Rachel has already said (further down the page in your own email) . The doc is right. You cannot drop a single datafile from a tablespace -Original Message- Sent: 29 October 2003 13:14 To: Multiple recipients of list ORACLE-L if you have a tablespace with 10 datafiles. can you drop just one datafile? From: Rachel Carmichael [EMAIL PROTECTED] Date: 2003/10/29 Wed AM 08:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: How to drop a datafile ? export the data (make sure you get all the data) drop the tablespace and recreate it import the data --- Prem Khanna J [EMAIL PROTECTED] wrote: Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
That won't actually do what you're implying. Checking the docs would prove helpful. From the SQL ref: If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile offline. However, the DROP clause does not remove the datafile from the database. To do that, you must drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE. If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword. Jared Venu Gopal [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/29/2003 05:09 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: How to drop a datafile ? Well, you an actually move all your objects from this datafile into a different datafile (read it as different TS) and then offline drop the datafile. This will ensure that users don't get the error 'xxx.dbf file is currently inacessible' message. Cheers! Venu -Original Message- Prem Khanna J Sent: Wednesday, October 29, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Thanx Sinardy Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael [EMAIL PROTECTED] wrote: The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
Prem.. Why don't you install Enterprise manager in any one of your client .. Login as Stand Alone.. And you can delete the same -Original Message- Sent: Tuesday, October 28, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Guys, it's oracle 9.2.0.3/Win2K. say for some reason (NOT recovery): i want to drop a datafile from a tablespace which has more than 1 datafile.how to do it ? Doc 111316.1 says u need to drop the tablespace or exp/imp the objects in the tablespace. your ideas please. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
--Boundary-00=_FF7IDL51VA40 Content-Type: Multipart/Alternative; boundary=Boundary-00=_FF7I8WA1VA40 --Boundary-00=_FF7I8WA1VA40 Content-Type: Text/Plain; charset=shift_jis Content-Transfer-Encoding: quoted-printable Hi Mudhalvan,=0D =0D i don't find any option to drop a datafile using OEM.=0D i would be more intereseted in doing it from sqlplus.=0D =0D Jp.=0D =0D ---Original Message---=0D =0D Prem..=0D =0D Why don't you install Enterprise manager in any one of your=0D client .. Login as Stand Alone.. And you can delete the same --Boundary-00=_FF7I8WA1VA40 Content-Type: Text/HTML; charset=shift_jis Content-Transfer-Encoding: quoted-printable HTMLHEAD META http-equiv=3DContent-Type content=3Dtext/html; charset=3Dshift_jis= META content=3DIncrediMail 1.0 name=3DGENERATOR !--IncrdiXMLRemarkStart IncrdiX-Info X-FIDFLAVOR00-NONE---/X-FID X-FVER3.0/X-FVER X-CNT;/X-CNT /IncrdiX-Info IncrdiXMLRemarkEnd-- /HEAD BODY style=3DBACKGROUND-POSITION: 0px 0px; FONT-SIZE: 12pt; MARGIN: 5px= 10px 10px; FONT-FAMILY: Arial bgColor=3D#ff background=3D scroll=3D= yes X-FVER=3D3.0 ORGYPOS=3D0 TABLE id=3DINCREDIMAINTABLE cellSpacing=3D0 cellPadding=3D2 width=3D100= % border=3D0 TBODY TR TD id=3DINCREDITEXTREGION style=3DFONT-SIZE: 12pt; CURSOR: auto; FONT-F= AMILY: Arial width=3D100% DIVFONT size=3D2Hi Mudhalvan,/FONT/DIV DIVFONT size=3D2/FONTnbsp;/DIV DIVFONT size=3D2i don't find anynbsp;option to drop a datafilenbsp;= using OEM./FONT/DIV DIVFONT size=3D2i would be more intereseted in doing it from sqlplus.= /FONT/DIV DIVFONT size=3D2/FONTnbsp;/DIV DIVFONT size=3D2Jp./FONT/DIV DIVFONT size=3D2nbsp;/FONT/DIV DIV id=3DIncrediOriginalMessageIFONT size=3D2---Original Messag= e---/FONT/I/DIV DIVFONT size=3D2nbsp;/FONT/DIV DIV id=3DreceivestringsFONT size=3D2Prem../FONT/DIV DIVFONT size=3D2nbsp;/FONT/DIV DIVFONT size=3D2Why don't you install Enterprise manager in any one o= f your/FONT/DIV DIVFONT size=3D2client .. Login as Stand Alone.. And you can delete t= he same/FONT/DIV DIVFONT size=3D2/FONTnbsp;/DIV/TD/TR TR TD id=3DINCREDIFOOTER width=3D100% TABLE cellSpacing=3D0 cellPadding=3D0 width=3D100% TBODY TR TD width=3D100%/TD TD id=3DINCREDISOUND vAlign=3Dbottom align=3Dmiddle/TD TD id=3DINCREDIANIM vAlign=3Dbottom align=3Dmiddle/TD/TR/TBODY/T= ABLE/TD/TR/TBODY/TABLESPAN id=3DIncrediStampSPAN dir=3DltrF= ONT face=3DArial, Helvetica, sans-serif size=3D2__= __BRFONT face=3DComic Sans MS size=3D2= A href=3Dhttp://www.incredimail.com/redir.asp?ad_id=3D309amp;lang=3D9;= IMG alt=3D hspace=3D0 src=3Dcid:371A5E9F-59E0-4BA4-BEF9-7FEE0744F93E= align=3Dbaseline border=3D0/Anbsp; IIncrediMail/I - BEmail has= finally evolved/B - /FONTA href=3Dhttp://www.incredimail.com/redir= =2Easp?ad_id=3D309amp;lang=3D9FONT face=3DTimes New Roman size=3D3= BUClick Here/U/B/FONT/A/SPAN/SPAN/FONT/BODY/HTML --Boundary-00=_FF7I8WA1VA40-- --Boundary-00=_FF7IDL51VA40 Content-Type: image/gif; name=IMSTP.gif Content-Transfer-Encoding: base64 Content-ID: 371A5E9F-59E0-4BA4-BEF9-7FEE0744F93E R0lGODlhFAAPALMIAP9gAM9gAM8vAM9gL/+QL5AvAGAvAP9gLwAA ACH/C05FVFNDQVBFMi4wAwEh+QQJFAAIACwAFAAPAAAEVRDJSaudJuudrxlEKI6B URlCUYyjKpgYAKSgOBSCDEuGDKgrAtC3Q/R+hkPJEDgYCjpKr5A8WK9OaPFZwHoPqm3366VKyeRt E30tVVRscMHDqV/u+AgAIfkEBWQACAAsABQADwAABBIQyUmrvTjrzbv/YCiOZGmeaAQAIfkE CRQACAAsAgABABAADQAABEoQIUOrpXIOwrsPxiQUheeRAgUA49YNhbCqK1kS9grQhXGAhsDBUJgZ AL2Dcqkk7ogFpvRAokSn0p4PO6UIuUsQggSmFjKXdAgRAQAh+QQFCgAIACwAFAAPAAAEEhDJ Sau9OOvNu/9gKI5kaZ5oBAAh+QQJFAAIACwCAAEAEAANAAAEShAhQ6ulcg7Cuw/GJBSF55ECBQDj 1g2FsKorWRL2CtCFcYCGwMFQmBkAvYNyqSTuiAWm9ECiRKfSng87pQi5SxCCBKYWMpd0CBEBACH5 BAVkAAgALAAUAA8AAAQSEMlJq7046827/2AojmRpnmgEADs= --Boundary-00=_FF7IDL51VA40-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
Title: Message Prem Please check this Screen.. If you still have doubt please let me know -Original Message-From: Prem Khanna J [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 9:33 PMTo: [EMAIL PROTECTED]Cc: Mudhalvan, MoovarkkuSubject: RE: How to drop a datafile ? Hi Mudhalvan, i don't find anyoption to drop a datafileusing OEM. i would be more intereseted in doing it from sqlplus. Jp. ---Original Message--- Prem.. Why don't you install Enterprise manager in any one of your client .. Login as Stand Alone.. And you can delete the same IncrediMail - Email has finally evolved - Click Here IMSTP.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Remove datafile_16.bmp
RE: How to drop a datafile ?
--Boundary-00=_ZH8IYHI1VA40 Content-Type: Multipart/Alternative; boundary=Boundary-00=_ZH8ITSM1VA40 --Boundary-00=_ZH8ITSM1VA40 Content-Type: Text/Plain; charset=shift_jis Content-Transfer-Encoding: quoted-printable Mudhalvan , the delete datafile icon in that screen=0D of OEM is not enabled . even if i take the datafile offline,=0D it is not enabled.=0D =0D even in ur screen it looks so ? isn't it ?=0D =0D Jp.=0D ---Original Message---=0D =0D Prem =0D =0D Please check this Screen.. If you still have doubt please let me know= =0D =20 --Boundary-00=_ZH8ITSM1VA40 Content-Type: Text/HTML; charset=shift_jis Content-Transfer-Encoding: quoted-printable HTMLHEAD META http-equiv=3DContent-Type content=3Dtext/html; charset=3Dshift_jis= META content=3DIncrediMail 1.0 name=3DGENERATOR !--IncrdiXMLRemarkStart IncrdiX-Info X-FIDFLAVOR00-NONE---/X-FID X-FVER/X-FVER X-CNT;/X-CNT /IncrdiX-Info IncrdiXMLRemarkEnd-- /HEAD BODY style=3DBACKGROUND-POSITION: 0px 0px; FONT-SIZE: 12pt; MARGIN: 5px= 10px 10px; FONT-FAMILY: Arial bgColor=3D#ff background=3D scroll=3D= yes ORGYPOS=3D0 X-FVER=3D3.0 TABLE id=3DINCREDIMAINTABLE cellSpacing=3D0 cellPadding=3D2 width=3D100= % border=3D0 TBODY TR TD id=3DINCREDITEXTREGION style=3DFONT-SIZE: 12pt; CURSOR: auto; FONT-F= AMILY: Arial width=3D100% DIVMudhalvan ,nbsp;the delete datafilenbsp; icon in that screen/DI= V DIVof OEM is not enabled . even if i take the datafile offline,/DIV DIVit is not enabled./DIV DIVnbsp;/DIV DIVeven in ur screen it looks so ? isn't it ?/DIV DIVnbsp;/DIV DIVJp./DIV DIV id=3DIncrediOriginalMessageI---Original Message---/I/D= IV DIVnbsp;/DIV DIV id=3Dreceivestrings DIV dir=3Dltr style=3DFONT-SIZE: 11pt iSPAN class=3D284343705-29102= 003FONT size=3D2Prem /FONT/SPAN/DIV/DIV DIVSPAN class=3D284343705-29102003FONT size=3D2/FONT/SPANnbsp;= /DIV DIVSPAN class=3D284343705-29102003nbsp;nbsp;nbsp; FONT size=3D2P= lease check this Screen.. If you still have doubt please let me know/FON= T/SPAN/DIV DIVSPAN class=3D284343705-29102003FONT size=3D2/FONT/SPANnbsp;= /DIV DIVnbsp;/DIV/TD/TR TR TD id=3DINCREDIFOOTER width=3D100% TABLE cellSpacing=3D0 cellPadding=3D0 width=3D100% TBODY TR TD width=3D100%/TD TD id=3DINCREDISOUND vAlign=3Dbottom align=3Dmiddle/TD TD id=3DINCREDIANIM vAlign=3Dbottom align=3Dmiddle/TD/TR/TBODY/T= ABLE/TD/TR/TBODY/TABLESPAN id=3DIncrediStampSPAN dir=3DltrF= ONT face=3DArial, Helvetica, sans-serif size=3D2__= __BRFONT face=3DComic Sans MS size=3D2= A href=3Dhttp://www.incredimail.com/redir.asp?ad_id=3D309amp;lang=3D9;= IMG alt=3D hspace=3D0 src=3Dcid:99EFB507-7B97-4C97-AAE3-F77F37CDAF34= align=3Dbaseline border=3D0/Anbsp; IIncrediMail/I - BEmail has= finally evolved/B - /FONTA href=3Dhttp://www.incredimail.com/redir= =2Easp?ad_id=3D309amp;lang=3D9FONT face=3DTimes New Roman size=3D3= BUClick Here/U/B/FONT/A/SPAN/SPAN/FONT/BODY/HTML --Boundary-00=_ZH8ITSM1VA40-- --Boundary-00=_ZH8IYHI1VA40 Content-Type: image/gif; name=IMSTP.gif Content-Transfer-Encoding: base64 Content-ID: 99EFB507-7B97-4C97-AAE3-F77F37CDAF34 R0lGODlhFAAPALMIAP9gAM9gAM8vAM9gL/+QL5AvAGAvAP9gLwAA ACH/C05FVFNDQVBFMi4wAwEh+QQJFAAIACwAFAAPAAAEVRDJSaudJuudrxlEKI6B URlCUYyjKpgYAKSgOBSCDEuGDKgrAtC3Q/R+hkPJEDgYCjpKr5A8WK9OaPFZwHoPqm3366VKyeRt E30tVVRscMHDqV/u+AgAIfkEBWQACAAsABQADwAABBIQyUmrvTjrzbv/YCiOZGmeaAQAIfkE CRQACAAsAgABABAADQAABEoQIUOrpXIOwrsPxiQUheeRAgUA49YNhbCqK1kS9grQhXGAhsDBUJgZ AL2Dcqkk7ogFpvRAokSn0p4PO6UIuUsQggSmFjKXdAgRAQAh+QQFCgAIACwAFAAPAAAEEhDJ Sau9OOvNu/9gKI5kaZ5oBAAh+QQJFAAIACwCAAEAEAANAAAEShAhQ6ulcg7Cuw/GJBSF55ECBQDj 1g2FsKorWRL2CtCFcYCGwMFQmBkAvYNyqSTuiAWm9ECiRKfSng87pQi5SxCCBKYWMpd0CBEBACH5 BAVkAAgALAAUAA8AAAQSEMlJq7046827/2AojmRpnmgEADs= --Boundary-00=_ZH8IYHI1VA40-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: How to drop a datafile ?
doc 111316.1 doesn't mention about using OEM for the same. so i hope it cannot be done with OEM. am i right LIST ? so how do i do it from sqlplus ? TIA. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: How to drop a datafile from a tablespace quickly
you wrote: there to claim his 42 virgins in Paradise? This is a common mistake. Read the following to know the truth: Humor . . . bin Laden's fate? After getting nailed by a Daisy Cutter, Osama made his way to the Pearly Gates. There, he is greeted by George Washington. How dare you attack the nation I helped conceive! yells Mr.Washington, slapping Osama in the face. Patrick Henry comes up from behind. You wanted to end America's liberty, so they gave you death. Henry punches Osama in the face. James Madison comes up next and says, This is why we allowed the federal Government to provide for the common defense and kicks Osama in the groin. Osama is the subject to similar beatings from John Randolph of Roanoke, James Monroe and 67 other people who have the same love for liberty and America. As Osama writhes in pain on the ground, Thomas Jefferson picks him up and hurls him back to the gate to be judged. As Osama awaits his journey to his final very hot destination, he screams This is not what I was promised! An angel replies I told you there would be 72 Virginians waiting for you. What did you think I said? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 11:13 PM I did not know that. Didn't Douglas Adams recently adjourn to the right hand of Hubble, there to claim his 42 virgins in Paradise? Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Tim Gorman [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:38 PM To: Multiple recipients of list ORACLE-L Subject: Re: How to drop a datafile from a tablespace quickly Scott, Did you hear that Douglas Adams was asked recently (i.e. a few years ago) how he knew the Hubble Constant was (roughly) 42, back when he wrote the Hitchhiker's Guide To The Galaxy series back in the 60s and 70s? The Hubble Constant (having to do with the rate of expansion of the universe, I think) had just been discovered to be 42, sometime in the mid-90s, I believe... His response was something along the lines of Well, actually I didn't know, of course. It's just that I needed an answer to the question 'What is the meaning of life, the universe, and everything?' and 42 seemed like an appropriate answer. Just an ordinary, sort of smallish number... I have to go back and re-read those... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 2:03 PM Exactly my point. The answer is 42. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Yechiel Adar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Umm.. couple of things to verify... First, yes, it is me again! Second, the U-Turn was done on a yellow light (at least that is what I see that night!) Third, that is just a LA street, not a highway! :P Forth, this is not wild... just LA type of driving! Hey Welcome back Winnie! John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Thanks, Winnie! It works.
I have a further quetion. If the archivelogs from time when the file was
created to current were lost, do we still have someway to bring the file
online?
Tom
-Original Message-
Sent: Wednesday, May 15, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
om cc:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject: RE: How to drop a
datafile from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Xie, Tom
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you
RE: How to drop a datafile from a tablespace quickly
Is that Winnie Liu?? The LA wild women street driver? Doing u-turns on a
red light across a 4 lane highway??
Good to see you again.
-Original Message-
Sent: Friday, May 17, 2002 12:28 PM
To: Multiple recipients of list ORACLE-L
Tom,
If you lost an archivelog from the time this datafile was created to present
time. There is no way to simply offline that file and recover that file. It
will
become a classic case of performing incomplete recovery then.
Glad that it works out for you! :D
WInnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: RE: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 08:38 AM
Please respond to
ORACLE-L
Thanks, Winnie! It works.
I have a further quetion. If the archivelogs from time when the file was
created to current were lost, do we still have someway to bring the file
online?
Tom
-Original Message-
Sent: Wednesday, May 15, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
om cc:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject: RE: How to drop a
datafile from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name
RE: How to drop a datafile from a tablespace quickly
Tom,
If you lost an archivelog from the time this datafile was created to present
time. There is no way to simply offline that file and recover that file. It will
become a classic case of performing incomplete recovery then.
Glad that it works out for you! :D
WInnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: RE: How to drop a datafile
from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 08:38 AM
Please respond to
ORACLE-L
Thanks, Winnie! It works.
I have a further quetion. If the archivelogs from time when the file was
created to current were lost, do we still have someway to bring the file
online?
Tom
-Original Message-
Sent: Wednesday, May 15, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
om cc:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject: RE: How to drop a
datafile from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
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
RE: How to drop a datafile from a tablespace quickly
Umm.. couple of things to verify...
First, yes, it is me again!
Second, the U-Turn was done on a yellow light (at least that is what I see that
night!)
Third, that is just a LA street, not a highway! :P
Forth, this is not wild... just LA type of driving!
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Grabowy, Chris
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
cc:
Sent by: Subject: RE: How to drop a datafile
from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 09:58 AM
Please respond to
ORACLE-L
Is that Winnie Liu?? The LA wild women street driver? Doing u-turns on a
red light across a 4 lane highway??
Good to see you again.
-Original Message-
Sent: Friday, May 17, 2002 12:28 PM
To: Multiple recipients of list ORACLE-L
Tom,
If you lost an archivelog from the time this datafile was created to present
time. There is no way to simply offline that file and recover that file. It
will
become a classic case of performing incomplete recovery then.
Glad that it works out for you! :D
WInnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: RE: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 08:38 AM
Please respond to
ORACLE-L
Thanks, Winnie! It works.
I have a further quetion. If the archivelogs from time when the file was
created to current were lost, do we still have someway to bring the file
online?
Tom
-Original Message-
Sent: Wednesday, May 15, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
om cc:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject: RE: How to drop a
datafile from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile
RE: How to drop a datafile from a tablespace quickly
Yeah, I have missed every OOW since then. Just not worth it compared to
IOUG.
I could have sworn I saw some Perl-looking DBA standing on the corner with
his eyes wide and mouth hanging open when she flung that car and her
passengers around.
BTW, when is that Perl/Oracle DBA book coming out. Heck, I'm still trying
to figure out how to write a loop in Perl...can't I just replace all those
strange characters with words?
-Original Message-
Sent: Friday, May 17, 2002 12:17 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Yeah, that's her!
I fortunately missed that little excursion. I believe that I
was a pedestrian that evening. ;)
Jared
Grabowy, Chris [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/17/2002 09:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: How to drop a datafile from a tablespace quickly
Is that Winnie Liu?? The LA wild women street driver? Doing u-turns on a
red light across a 4 lane highway??
Good to see you again.
-Original Message-
Sent: Friday, May 17, 2002 12:28 PM
To: Multiple recipients of list ORACLE-L
Tom,
If you lost an archivelog from the time this datafile was created to
present
time. There is no way to simply offline that file and recover that file.
It
will
become a classic case of performing incomplete recovery then.
Glad that it works out for you! :D
WInnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple
recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: RE: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 08:38 AM
Please respond to
ORACLE-L
Thanks, Winnie! It works.
I have a further quetion. If the archivelogs from time when the file was
created to current were lost, do we still have someway to bring the file
online?
Tom
-Original Message-
Sent: Wednesday, May 15, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
om cc:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject: RE: How to drop a
datafile from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple
recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
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
RE: How to drop a datafile from a tablespace quickly
Just make sure you have a good recovery plan!
Waleed
-Original Message-
Sent: Friday, May 17, 2002 2:01 PM
To: Multiple recipients of list ORACLE-L
Umm.. couple of things to verify...
First, yes, it is me again!
Second, the U-Turn was done on a yellow light (at least that is what I see
that
night!)
Third, that is just a LA street, not a highway! :P
Forth, this is not wild... just LA type of driving!
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Grabowy, Chris
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
cc:
Sent by: Subject: RE: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 09:58 AM
Please respond to
ORACLE-L
Is that Winnie Liu?? The LA wild women street driver? Doing u-turns on a
red light across a 4 lane highway??
Good to see you again.
-Original Message-
Sent: Friday, May 17, 2002 12:28 PM
To: Multiple recipients of list ORACLE-L
Tom,
If you lost an archivelog from the time this datafile was created to present
time. There is no way to simply offline that file and recover that file. It
will
become a classic case of performing incomplete recovery then.
Glad that it works out for you! :D
WInnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: RE: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/17/02 08:38 AM
Please respond to
ORACLE-L
Thanks, Winnie! It works.
I have a further quetion. If the archivelogs from time when the file was
created to current were lost, do we still have someway to bring the file
online?
Tom
-Original Message-
Sent: Wednesday, May 15, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
om cc:
'[EMAIL PROTECTED]' [EMAIL PROTECTED]
Subject: RE: How to drop a
datafile from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
RE: How to drop a datafile from a tablespace quickly
No. Get some downtime, quickly(!) before data does get written to that file. Go through the export/drop tablespace/recreate. BTW, do you know _why_ the file header is corrupted? Is there a disk hardware problem? You are gonna have downtime sooner or later. Tell damagement to get over it. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Xie, Tom [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: How to drop a datafile from a tablespace quickly 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Xie, Tom
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Umm.. Try this:
1) ALTER DATABASE DATAFILE 'filename' OFFLINE;
2) get rid of the offended datafile from OS
3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)';
4) RECOVER DATAFILE 'filename';
5) ALTER DATABASE DATAFILE 'filename' ONLINE;
Winnie
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: '[EMAIL PROTECTED]'
[EMAIL PROTECTED]
om cc: '[EMAIL PROTECTED]'
[EMAIL PROTECTED]
Subject: RE: How to drop a datafile
from a tablespace quickly
05/15/02 09:32 AM
Since there is no data in the file, can we make a datafile to replace it?
Tom Xie
-Original Message-
Sent: Wednesday, May 15, 2002 11:25 AM
To: [EMAIL PROTECTED]
There is absolutely NO WAY to drop a datafile from a tablespace at all.
--
\ /~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~
(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~
/ V \ Oracle Database Administrator`~`~
o--m-m--o Infonet Services Corporation `~`~
# mailto:[EMAIL PROTECTED]`~`~
~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~
Xie, Tom
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
om cc:
Sent by: Subject: How to drop a
datafile from a tablespace quickly
[EMAIL PROTECTED]
05/15/02 09:58 AM
Please respond to
ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Scott - I agree with your advice. Could he take the bad datafile offline to prevent Oracle from writing to it (until he rebuilds the table)? Would that cause any other problems that I am overlooking? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L No. Get some downtime, quickly(!) before data does get written to that file. Go through the export/drop tablespace/recreate. BTW, do you know _why_ the file header is corrupted? Is there a disk hardware problem? You are gonna have downtime sooner or later. Tell damagement to get over it. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Xie, Tom [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: How to drop a datafile from a tablespace quickly 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Not that I'm aware of. Sounds like an interesting idea, but one I've never tried. I just told the users there was a hardware problem that necessitated oracle coming down (whether there was one or not). Dishonest? Maybe, but it bought me the brief, immediate downtime necessary to prevent more serious problems in the future. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to drop a datafile from a tablespace quickly Scott - I agree with your advice. Could he take the bad datafile offline to prevent Oracle from writing to it (until he rebuilds the table)? Would that cause any other problems that I am overlooking? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L No. Get some downtime, quickly(!) before data does get written to that file. Go through the export/drop tablespace/recreate. BTW, do you know _why_ the file header is corrupted? Is there a disk hardware problem? You are gonna have downtime sooner or later. Tell damagement to get over it. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Xie, Tom [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject:How to drop a datafile from a tablespace quickly 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: How to drop a datafile from a tablespace quickly
there is a hardware problem that necessitated Oracle coming down. the only question is does the database come down cleanly when YOU want it to or does it come down with a crash, time undetermined, when the file is accessed? |+--- || | || | || [EMAIL PROTECTED]| || ms.osd.mil | || | || 05/15/2002 02:39 PM | || Please respond to| || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: RE: How to drop a datafile from | | a tablespace quickly | | Not that I'm aware of. Sounds like an interesting idea, but one I've never tried. I just told the users there was a hardware problem that necessitated oracle coming down (whether there was one or not). Dishonest? Maybe, but it bought me the brief, immediate downtime necessary to prevent more serious problems in the future. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to drop a datafile from a tablespace quickly Scott - I agree with your advice. Could he take the bad datafile offline to prevent Oracle from writing to it (until he rebuilds the table)? Would that cause any other problems that I am overlooking? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L No. Get some downtime, quickly(!) before data does get written to that file. Go through the export/drop tablespace/recreate. BTW, do you know _why_ the file header is corrupted? Is there a disk hardware problem? You are gonna have downtime sooner or later. Tell damagement to get over it. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Xie, Tom [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: How to drop a datafile from a tablespace quickly 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX
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:[EMAIL PROTECTED]] 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@vicr.| || com | || | || 05/15/2002 | || 01:48 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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,
RE: How to drop a datafile from a tablespace quickly
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 [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
Exactly my point. The answer is 42. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to drop a datafile from a tablespace quickly there is a hardware problem that necessitated Oracle coming down. the only question is does the database come down cleanly when YOU want it to or does it come down with a crash, time undetermined, when the file is accessed? |+--- || | || | || [EMAIL PROTECTED]| || ms.osd.mil | || | || 05/15/2002 02:39 PM | || Please respond to| || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: RE: How to drop a datafile from | | a tablespace quickly | | Not that I'm aware of. Sounds like an interesting idea, but one I've never tried. I just told the users there was a hardware problem that necessitated oracle coming down (whether there was one or not). Dishonest? Maybe, but it bought me the brief, immediate downtime necessary to prevent more serious problems in the future. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to drop a datafile from a tablespace quickly Scott - I agree with your advice. Could he take the bad datafile offline to prevent Oracle from writing to it (until he rebuilds the table)? Would that cause any other problems that I am overlooking? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L No. Get some downtime, quickly(!) before data does get written to that file. Go through the export/drop tablespace/recreate. BTW, do you know _why_ the file header is corrupted? Is there a disk hardware problem? You are gonna have downtime sooner or later. Tell damagement to get over it. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Xie, Tom [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: How to drop a datafile from a tablespace quickly 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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
Re: How to drop a datafile from a tablespace quickly
Scott, Did you hear that Douglas Adams was asked recently (i.e. a few years ago) how he knew the Hubble Constant was (roughly) 42, back when he wrote the Hitchhiker's Guide To The Galaxy series back in the 60s and 70s? The Hubble Constant (having to do with the rate of expansion of the universe, I think) had just been discovered to be 42, sometime in the mid-90s, I believe... His response was something along the lines of Well, actually I didn't know, of course. It's just that I needed an answer to the question 'What is the meaning of life, the universe, and everything?' and 42 seemed like an appropriate answer. Just an ordinary, sort of smallish number... I have to go back and re-read those... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 2:03 PM Exactly my point. The answer is 42. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to drop a datafile from a tablespace quickly there is a hardware problem that necessitated Oracle coming down. the only question is does the database come down cleanly when YOU want it to or does it come down with a crash, time undetermined, when the file is accessed? |+--- || | || | || [EMAIL PROTECTED]| || ms.osd.mil | || | || 05/15/2002 02:39 PM | || Please respond to| || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: RE: How to drop a datafile from | | a tablespace quickly | | Not that I'm aware of. Sounds like an interesting idea, but one I've never tried. I just told the users there was a hardware problem that necessitated oracle coming down (whether there was one or not). Dishonest? Maybe, but it bought me the brief, immediate downtime necessary to prevent more serious problems in the future. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to drop a datafile from a tablespace quickly Scott - I agree with your advice. Could he take the bad datafile offline to prevent Oracle from writing to it (until he rebuilds the table)? Would that cause any other problems that I am overlooking? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L No. Get some downtime, quickly(!) before data does get written to that file. Go through the export/drop tablespace/recreate. BTW, do you know _why_ the file header is corrupted? Is there a disk hardware problem? You are gonna have downtime sooner or later. Tell damagement to get over it. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Xie, Tom [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: How to drop a datafile from a tablespace quickly 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: [EMAIL PROTECTED
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 |+ ||| ||| || [EMAIL PROTECTED]| || tate.ny.us| ||| || 05/15/2002| || 03:53 PM | || Please respond| || to ORACLE-L | ||| |+ | || | To: [EMAIL PROTECTED] | | 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:[EMAIL PROTECTED]] 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@vicr.| || com | || | || 05/15/2002 | || 01:48 PM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: How to drop a datafile from a tablespace quickly
I did not know that. Didn't Douglas Adams recently adjourn to the right hand of Hubble, there to claim his 42 virgins in Paradise? Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Tim Gorman [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:38 PM To: Multiple recipients of list ORACLE-L Subject: Re: How to drop a datafile from a tablespace quickly Scott, Did you hear that Douglas Adams was asked recently (i.e. a few years ago) how he knew the Hubble Constant was (roughly) 42, back when he wrote the Hitchhiker's Guide To The Galaxy series back in the 60s and 70s? The Hubble Constant (having to do with the rate of expansion of the universe, I think) had just been discovered to be 42, sometime in the mid-90s, I believe... His response was something along the lines of Well, actually I didn't know, of course. It's just that I needed an answer to the question 'What is the meaning of life, the universe, and everything?' and 42 seemed like an appropriate answer. Just an ordinary, sort of smallish number... I have to go back and re-read those... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 2:03 PM Exactly my point. The answer is 42. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to drop a datafile from a tablespace quickly
I did not know that. We're not worthy! We're not worthy! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 3:13 PM I did not know that. Didn't Douglas Adams recently adjourn to the right hand of Hubble, there to claim his 42 virgins in Paradise? Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Tim Gorman [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:38 PM To: Multiple recipients of list ORACLE-L Subject: Re: How to drop a datafile from a tablespace quickly Scott, Did you hear that Douglas Adams was asked recently (i.e. a few years ago) how he knew the Hubble Constant was (roughly) 42, back when he wrote the Hitchhiker's Guide To The Galaxy series back in the 60s and 70s? The Hubble Constant (having to do with the rate of expansion of the universe, I think) had just been discovered to be 42, sometime in the mid-90s, I believe... His response was something along the lines of Well, actually I didn't know, of course. It's just that I needed an answer to the question 'What is the meaning of life, the universe, and everything?' and 42 seemed like an appropriate answer. Just an ordinary, sort of smallish number... I have to go back and re-read those... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 2:03 PM Exactly my point. The answer is 42. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to drop a datafile from a tablespace quickly
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
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 [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Kimberly Smith
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: How to drop a datafile from a tablespace quickly
No easy way to drop a datafile.
Your best bet would be to resize the datafile to a very small size
( eg 10k).
(This will cause a trivial performance overhead since it has to
checkpoint an extra
file. But not even worth mentioning)
And then add another datafile to this TableSpace or rebuild the
tablespace when
you have some time with the appropiate size datafiles.
Regards
Suhen
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 [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Kimberly Smith
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
