The Oracle (tm) Users' Co-Operative FAQ

Recovering from a loss datafile in index tablespace

Back to index of questions

There is no easy way as to drop a datafile of a tablespace. The only way to remove a datafile from a database is to drop the defining tablespace. There are a few steps to follow:

If you think the index tablespace can be easily created

1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline drop
3. open the database - alter database open 
4. drop the index tablespace - drop tablespace xxx including contents;
5. Recreate the index tablespace
6. Recreate the index in the index tablespace

To recreate the index tablespace is way too tedious and time consuming

1. Restore a good copy of datafile
2. Mount the database - startup mount 
3. Recover the datafile - recover datafile 'fullpath/filename'
4. You will be prompted for archived log. Confirm until you receive "Media Recovery Complete"

(PS. note that if you are running in noarchivelog mode, you can only recover to the point within the range of your online logs. Normally, to have all your online logs intact without being overwritten is quite unlikely and therefore not illustrated here!)

Further reading: Metalink Note:1013115.6 Recovering from a Lost Datafile in an Index Tablespace

Back to top

Back to index of questions