How to drop tablespace with missing datafile
Oracle expert Brian Peasland explains how to drop a tablespace with a missing datafile and recreate the tablespace.
I lost a hard drive containing a tablespace for my Oracle instance. It was not the drive that the OS and Oracle were installed to, just an extra datafile added when the first drive was running low on tablespace. The problem now is that I cannot get Oracle to start completely. I don't really care about the data that was on this drive, I just want to get Oracle back up and create a new tablespace to replace the one I lost. I've seen articles on recreating missing dbf files that are accidentally deleted, but just creating a new datafile with the same name doesn't work because other files, like logs, are missing as well. Any suggestions on how to approach this?
Try the following:
shutdown abort
startup mount
alter database datafile '/directory/filename' offline drop;
alter database open;
drop tablespace ts_name;
In the above steps, you tell Oracle that the missing datafile is now missing (offline drop). You should then be able to open the database. Once open, you should be able to drop the tablespace with the missing datafile. You can then recreate the tablespace.
