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.

1. Run SQL*Plus.
2. Connect to database as SYSDBA with this query:

   CONNECT / AS SYSDBA

3. Mount the database instead of starting it up:

   STARTUP MOUNT;

4. 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.

5. Now start the database proper:

   ALTER DATABASE OPEN;

6. As the tablespace has damaged, drop it to recreate from fresh backup.

   DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

7. Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.

   Tip: How to check datafiles that belong to tablespace.

8. Continue with the recovery process.

Share and contribute or get technical support and help at My Digital Life Forums.

Related Articles

• How to Remove and Drop Datafiles from Tablespace in Oracle Database
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
• How to Rename or Move Oracle Tablespace Datafile to Another Location
• ORA-25153 Temporary Tablespace is Empty Error in Oracle
• Delete, Remove or Drop Oracle Stored Packages with Drop Package
• Oracle Database Link
• ORA-02449 Oracle Drop Table Error
• Manual and Clean Uninstall Oracle for Windows
• Oracle Database Import Error 3113/3114
• Change Oracle Database User Password

This entry was posted on Saturday, August 4th, 2007 at 2:55 am and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.