Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to drop a datafile from a tablespace quickly

RE: How to drop a datafile from a tablespace quickly

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 17 May 2002 10:13:35 -0800
Message-ID: <F001.00463988.20020517101335@fatcity.com> 





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:winnie_liu_at_infonet.com        `~`~


    ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~



 


                      "Grabowy, Chris"

                      <cgrabowy_at_fcg.com        To:       Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>            
                      >                        cc:

                      Sent by:                 Subject:  RE: How to drop a
datafile from a tablespace quickly                   
                      root_at_fatcity.com

 

 

                      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:winnie_liu_at_infonet.com        `~`~


    ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~






                      "Xie, Tom"

                      <TXie_at_taylorpub.c        To:       Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>
                      om>                      cc:

                      Sent by:                 Subject:  RE: How to drop a
datafile from a tablespace quickly
                      root_at_fatcity.com





                      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:winnie_liu_at_infonet.com        `~`~


    ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~






                      "Xie, Tom"

                      <TXie_at_taylorpub.c        To:
"'Winnie_Liu_at_infonet.com'" <Winnie_Liu_at_infonet.com>
                      om>                      cc:
"'ORACLE-L_at_fatcity.com'" <ORACLE-L_at_fatcity.com>
                                               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: txie_at_taylorpub.com





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:winnie_liu_at_infonet.com        `~`~


    ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~






                      "Xie, Tom"

                      <TXie_at_taylorpub.c        To:       Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>
                      om>                      cc:

                      Sent by:                 Subject:  How to drop a
datafile from a tablespace quickly
                      root_at_fatcity.com





                      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: 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: Winnie_Liu_at_infonet.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: 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: Winnie_Liu_at_infonet.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: Grabowy, Chris


  INET: cgrabowy_at_fcg.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: Winnie_Liu_at_infonet.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: Khedr, Waleed


  INET: Waleed.Khedr_at_FMR.COM


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri May 17 2002 - 13:13:35 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US