solution for undo tablespace 100% full in noarchive mode

jonathanwu

When I prepare a testing environment for data warehouse with huge data volume, A lots of DML and indexes should be done. There is not enough disk space and the UNDO is not as big as you wish. I found a way to solve it and I'd like to discuss with you.

====================
Problem Description
====================
The SYS_UNDO tablespace is 100% full.
We want to drop it and then recreate it to shrink the free space.
The rollback segment has a status of PARTLY AVAILABLE.  I have tried off-lining the rollback segment and then dropping it, but that does not work.
The instance is up and running and the alert log shows the following messages repeatedly:
SMON: mark undo segment %v as available
SMON: about to recover undo segment %v
The database is running in nonarchive log mode, so there is no way to perform a recovery on the datafile containing the problem rollback segment.
Rather than letting the rollback segment keep trying to repeatedly recover an unavailable segment, you need a mechanism to stop this process so you can drop the bad rollback segment and get normal processing resumed with minimal data loss.
====================
Solution
====================
To do this, you need to be able to shutdown (possibly using abort) and restart the database in restricted mode.  No one is to be processing any transactions.   Then off-line and bring back on-line all the user tablespaces.   Once this is done, the problem rollback segments status will change to on-line. Then do shutdown immediate on the database and startup normal.     You will then be able to drop this rollback segment, and subsequently drop its associated tablespace.  The error messages will have gone away completely, along with the I/O problems.  Processing will return to normal without having lost data or by going back to older cold backups of the database.        
====================
Explanation
====================
Doing this off-lining and bringing back on-line of the tablespaces will reset the need for SMON to keep trying to recover the information in the corrupted  rollback segment.