How to drop corrupted “undo” tablespace segment of Oracle database?

Once it happened that “undo” tablespace segments got corrupted for some reason, here you’ll find a way to get rid of them. Corrupted tablespace segment status is indicated as NEEDS RECOVERY. Check how many of them you have:

 select tablespace_name, segment_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1' and status ='NEEDS RECOVERY';

Steps to drop corrupted segments:

1. Gather all segment names together and write them into system parameter _OFFLINE_ROLLBACK_SEGMENTS. Here is a statement which generates necessary SQL for setting parameter together with all segment names included:

    
   -- Source statement
   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'; 
   
   -- Generated statement 
   ALTER SYSTEM SET "_OFFLINE_ROLLBACK_SEGMENTS"='_SYSSMU20_565759258$','_SYSSMU21_2391030682$','_SYSSMU22_919698808$','_SYSSMU23_1368980304$','_SYSSMU24_3409580981$','_SYSSMU3_3285411314$','_SYSSMU4_4250244621$','_SYSSMU5_750802473$','_SYSSMU6_3167659685$','_SYSSMU7_2435451351$','_SYSSMU8_1462975257$' scope=spfile; 
   

2. As we write parameter to spfile, database restart is needed to take it effect. Using database server command line connect to DB instance as sysdba. Shutdown immediate and startup database instance:

   $sqlplus / as sysdba
   SQL>shutdown immediate
   ...
   SQL>startup
   

   If you experience issues by connecting to database instance directly from server, please check this post – Why can’t connect to Oracle instance using “sqlplus / as sysdba” ?
   You can also edit spfile directly in server filesystem. But you can’t edit it directly. How it is done, please check post – How to edit or fix corrupted spfile of Oracle database ?

3. Generate drop statements for every segment and execute each of them:

   -- Source statement
   select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where tablespace_name='UNDOTBS1' and status ='NEEDS RECOVERY';
   
   --Generated statements (example)
   drop rollback segment "_SYSSMU3_3285411314$";
   drop rollback segment "_SYSSMU4_4250244621$";
   drop rollback segment "_SYSSMU5_750802473$";
   ...
   

   Thats it. Now you can do whatever you need with undo tablespace. If you are struggling with dropping or shrinking it, please check this post – How to shrink Oracle database “undo” tablespace data file? .

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0