Q
Manage Learn to apply best practices and optimize your operations.

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.

This was last published in February 2008

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close