How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile
Accident may happens, even though it’s costly one. This is especially the case in Oracle database, which stores the database objects physically in data files on hard disk. Fast hand administrators may accidentally delete some of the datafiles, or the datafiles may get corrupted or unreadable on hard disk failure. You may get also some missing datafiles after database recovery process. In any case, missing a or a few datafiles will cause Oracle database to fail to startup, rendering the whole DB inaccessible.
The solution to the missing datafiles is to drop the affected tablespace where has incomplete datafiles, and then recreate the tablespace and import the data into the tablespace from backup. However, the steps are not so straight forward.
- Run SQL*Plus.
- Connect to database as SYSDBA with this query:
CONNECT / AS SYSDBA
- Mount the database instead of starting it up:
STARTUP MOUNT;
- Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:
ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;
Repeat the command for every datafiles that unaccounted for.
- Now start the database proper:
ALTER DATABASE OPEN;
- As the tablespace has damaged, drop it to recreate from fresh backup.
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;
- Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.
- Continue with the recovery process.
Related Articles
- How to Remove and Drop Datafiles from Tablespace in Oracle Database
- How to Rename or Move Oracle Tablespace Datafile to Another Location
- Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
- ORA-25153 Temporary Tablespace is Empty Error in Oracle
- Delete, Remove or Drop Oracle Stored Packages with Drop Package
- Change Oracle Database User Password
- Oracle Database Link
- ORA-02449 Oracle Drop Table Error
- Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
- Oracle Database Import Error 3113/3114
Entries (RSS)
October 16th, 2009 04:14
“How Drop Tablespace and Recover Oracle Database When Accidentally Delete tablespace?”
May 21st, 2009 10:08
Exactly what I needed! Thanks for saving what seemed to be a wretched issue!
April 20th, 2009 20:47
thanks a million.
u r a life saver
February 19th, 2009 06:49
This is a life saver …. this works fine in oracle 10G HP itenium (or any where else).
great doc.
thanks
Dewan Gazzali
December 17th, 2008 13:53
*2nds last post*
November 6th, 2008 06:15
You are a life saver!!!!!!
October 28th, 2008 03:09
I ran a script to create a user w/o checking it closely enough. Next thing you know, my db won’t start because it is looking for a data file that doesn’t exist. This tip helped me get around the error quickly. Thanks for posting it.
October 9th, 2008 02:31
Thank you for taking the time to post this very valuable information.
September 2nd, 2008 00:31
Thank you very much, this was really helpful.
I had problems dropping the tablespace, i had to drop the user also.
July 8th, 2008 19:31
This info helped us to a very great extent and hence we are able to proceed further smoothly.
April 2nd, 2008 18:27
These are only to drop the TS
but not to recover…
hence the worst….
August 4th, 2007 02:57
[...] If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped. (See instruction to recover from missing datafiles.) [...]