How to Drop Corrupted Undo Tablespace Segments in Oracle

If undo segments in the undo tablespace (like UNDOTBS1) become corrupted, their status appears as NEEDS RECOVERY. These segments can't be dropped directly unless taken offline first.

Step 1: Check for Corrupted Segments

Run this query to find the corrupted undo segments:


SELECT tablespace_name, segment_name, status 
FROM dba_rollback_segs 
WHERE tablespace_name = 'UNDOTBS1' 
AND status = 'NEEDS RECOVERY';

Step 2: Generate the Offline Segment Parameter

To take these segments offline, generate the following parameter setting using this query:


SELECT 
  'ALTER SYSTEM SET "_OFFLINE_ROLLBACK_SEGMENTS" = ' ||
  LISTAGG('''' || segment_name || '''', ',') 
  WITHIN GROUP (ORDER BY segment_name) || 
  ' SCOPE=SPFILE;' 
FROM dba_rollback_segs 
WHERE tablespace_name = 'UNDOTBS1' 
AND status = 'NEEDS RECOVERY';

This will output a statement like:


ALTER SYSTEM SET "_OFFLINE_ROLLBACK_SEGMENTS" = 
'_SYSSMU3_1987$', '_SYSSMU4_356$', '_SYSSMU5_9$' SCOPE=SPFILE;

Copy and run that output as SYSDBA.

Step 3: Restart the Database

Since the parameter was set in the SPFILE, restart the database to apply the change:


sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Step 4: Drop the Corrupted Rollback Segments

Now that the segments are offline, you can drop them. Generate the necessary SQL with this:


SELECT 
  'DROP ROLLBACK SEGMENT "' || segment_name || '";' 
FROM dba_rollback_segs 
WHERE tablespace_name = 'UNDOTBS1' 
AND status = 'NEEDS RECOVERY';

Then execute the generated DROP ROLLBACK SEGMENT statements one by one.

Optional: Drop the Undo Tablespace

If you're no longer using UNDOTBS1 and have already switched to a new undo tablespace, you can drop it:


DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Make sure the undo retention period has passed and no active session is using it.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

How to Drop Corrupted Undo Tablespace Segments in Oracle