Database

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


Advertisement

Leave a Reply

Your email address will not be published.