By
Published: 22 Feb 2008
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
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Continue Reading