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 to
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:
