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.

Dig Deeper on Oracle database administration

Data Management
Business Analytics
  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

  • How developers can avoid remote work scams

    Software developers can find good remote programming jobs, but some job offers are too good to be true. Follow these tips to spot...

Data Center
Content Management