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" <ORACLE-L_at_fatcity.com> 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: Rachel_Carmichael_at_Sonymusic.com
> > [SMTP:Rachel_Carmichael_at_Sonymusic.com]
> > 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?
> >
> >
> >
> >
> > |--------+------------------------------->
> > | | |
> > | | |
> > | | Scott.Shafer_at_dcpds.cp|
> > | | ms.osd.mil |
> > | | |
> > | | 05/15/2002 02:39 PM |
> > | | Please respond to |
> > | | ORACLE-L |
> > | | |
> > |--------+------------------------------->
> > >----------------------------------------------------|
> > | |
> > | To: ORACLE-L_at_fatcity.com |
> > | cc: (bcc: Rachel Carmichael) |
> > | Subject: RE: How to drop a datafile from |
> > | a tablespace quickly |
> > >----------------------------------------------------|
> >
> >
> >
> >
> > 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:DWILLIAMS_at_lifetouch.com]
> > > 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.
> > > dwilliams_at_lifetouch.com
> > >
> > >
> > > -----Original Message-----
> > > [mailto:Scott.Shafer_at_dcpds.cpms.osd.mil]
> > > 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:TXie_at_taylorpub.com]
> > > > 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: TXie_at_taylorpub.com
> > > >
> > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > > San Diego, California -- Public Internet access / Mailing
Lists
> > > > --------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed from). You may
> > > > also send the HELP command for other information (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author:
> > > INET: Scott.Shafer_at_dcpds.cpms.osd.mil
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: DENNIS WILLIAMS
> > > INET: DWILLIAMS_at_LIFETOUCH.COM
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Scott.Shafer_at_dcpds.cpms.osd.mil
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Rachel_Carmichael_at_Sonymusic.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Scott.Shafer_at_dcpds.cpms.osd.mil
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).