Re: Whoops! Deleted my db file before dropping tablespace! HELP!

Another little known option which I have had to use before is to re-create the missing datafile as an empty substitute for the deleted file. Use the following command:

    ALTER DATABASE CREATE DATAFILE ’disk1:users1’ AS ’disk2:users1’;

This will provide the database with a genuine, albeit empty, datafile that replaces the deleted file. If the file was just deleted, chances are good you can recover from the redo logs. If you archive your redo logs, you can definately recover the file from them. Even with just an empty file in place, you can drop the tablespace when the database is in exclusive mode.

    --Gary

Matthias Oswald wrote in message <36DF8FDE.C4699FC8_at_eurocontrol.be>...
>Hi all,
>
>I too did made this error once. ALTER DATABASE will work, but you have to
be in
>startup mount (or maybe nomount)
>for the command to work.
>
>Ken
>
>gremlin wrote:
>
>> *L* I did the same thing (ALSO a newbie) and got into a chicken-egg
problem. I
>> couldn't drop the tablespace because it couldn't find the dbf file. I
couldn't
>> THINK I tried alter database as suggested above but it didn't like that.
I
>> know I tried making a dummy .dbf and it didn't like the header.
Solution:
>> (You won't like it !) I dropped ALL .dbf, log files, ctl files, etc. and
>> recreated the database. It was a new one anyway. Obviously this is
rarely a
>> good solution!
>>
>> Good luck.
>>
>> Mike
>>
>> David Sisk wrote:
>>
>> > Hi:
>> >
>> > You have to issue a "ALTER DATABASE DROP DATAFILE......". After you do
>> > this, it will let you drop the tablespace (if I remember correctly!)
>> >
>> > Good luck,
>> >
>> > --
>> > David C. Sisk
>> > The Unofficial ORACLE on NT site
>> > http://www.ipass.net/~davesisk/oont.htm
>> >
>> > Keith Kwiatek wrote in message <7av0ef$7o0$1_at_news.nist.gov>...
>> > >Hello Guru person,
>> > >
>> > >I am a newbie...I wanted to drop a test tablespace I was playing with,
but
>> > I
>> > >deleted the oracle data file before I issued the "drop tablespace"
>> > >command.... Now when I try to "drop tablespace" it spits out a bunch
of
>> > >errors...
>> > >
>> > >How do I clean this up?
>> > >
>> > >Thanks!
>> > >
>> > >Keith
>> > >
>> > >
>>
>> --
>> -----------------------------------------------------
>> To reply to me via email, remove the "__NO_SPAM__" in
>> the header email address.
>> -----------------------------------------------------
>
>
>
>--
>Regards,
>
>Matthias Oswald Eurocontrol - CFMU
>
>matthias.oswald_at_eurocontrol.be Rue de la Fusee, 96
>Tel: +32 2 729 96 89 B-1130 Bruxelles
>Fax: +32 2 729 90 22 Belgium
>
>
Received on Mon Mar 08 1999 - 10:29:47 CST