How to delete a corrupted table space

oracleoracle-11g-r2tablespaces

Problem:
On an Oracle 11.2g DB;
We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager).

What can we do?
What other information is needed to diagnose the problem?

(We've meanwhile checked the file system, which is ok, but the DBF file for the tablespace is lost.)

Best Answer

If you just want to remove the tablespace have you tried logging in as sys and dropping it?

DROP TABLESPACE <tablespace>
INCLUDING CONTENTS 
CASCADE CONSTRAINTS;

If so, what error message do you receive? (I'm assuming you've made a cold backup of the database at this point in case the tablespace is needed.)

Alternatively, if you can determine which datafile is corrupt you can remove this from the tablespace:

ALTER DATABASE DATAFILE '<filename>' OFFLINE DROP;

Make sure that <filename> is the filename known to Oracle, especially if any links may be involved on disk! Otherwise you will get misleading errors about non-existent files.

Related Question