Re: Some plonker typed rm temp.dbf AAARRGGHHH!

welfers_at_my-dejanews.com wrote:

>A colleague decided to clear some space from a filesystem and deleted a dbf
>file. Not surprisingly oracle is bleating about it. How do I fix it?
>Fortunately the dbf was for temp tables and as far as I can make out nothing
>was using it anyway. I tried to drop the tablespace but oracle complained
>that it could not find the datafiles. I know how to create and drop
>tablespaces etc but I would have thought the first job would be to tell
>oracle that the temporary tablespace no longer exists before adding it again.
>Before you ask the answer is "no we don't have any backups or archive logs or
>exports." :(( Not my fault honest I did not know the machine existed until
>yesterday when I heard about the problem. It is my job to fix it so any help
>would be appreciated. Thanks in advance.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

This might ( and the emphasis is on might) - if you know the name and location of the deleted dbf file, create one with the operating system..... It might 'fool' Oracle enough to let you drop the tablespace - However, Oracle does stamp the header of its files, so it might dislike a 'foreign' file...

You could also try 'alter database backup control file to trace' to get a 'readable/editable' version of the control file...Look for the filenames used and delete the reference to the missing file - Then edit the control file to make it a Sql script....there are instructions in the file...

Shutdown the database...
Load Svrmgr ( or whatever your equivilant is ) and connect internal Run the sql you created above...This will start the database with the altered control file...If it works, shutdown the database and restart normally...

Good Luck....( Back up everything first...just in case; a bad database may be better than none )

John Greco
Oracle DBA Received on Wed Jul 15 1998 - 16:35:42 CDT