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:
- 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;
- 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 ?
- 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 18.104.22.168.0