undo segment NEEDS RECOVERY

SQL>SELECT SEGMENT_NAME,STATUS  FROM DBA_ROLLBACK_SEGS WHERE STATUS LIKE 'PART%' or status like 'NEED%';

SEGMENT_NAME                   STATUS

------------------------------ ----------------
_SYSSMU17$                     NEEDS RECOVERY

SQL>SELECT * FROM V$RECOVER_FILE;

     FILE# ONLINE  ONLINE_ ERROR                 CHANGE# TIME

---------- ------- ------- ------------------ ---------- ---------
         2 OFFLINE OFFLINE OFFLINE NORMAL              0

Here file#=2  this is offline, check the name of the datafile

SQL>SELECT NAME,STATUS FROM V$DATAFILE WHERE FILE#=2;

 

NAME                                                                   

----------------------------------------------------------------------

/undo/undotbs01.dbf         

 

Make the file 2 online

 

SQL>ALTER DATABASE DATAFILE 2 ONLINE;

                                            

Check the status again

 

STATUS  NAME

------- ------------------------------------------------------------

ONLINE  /undo/undotbs01.dbf

 

SQL>SELECT * FROM V$RECOVER_FILE;

no rows selected

SQL>SELECT SEGMENT_NAME,STATUS  FROM DBA_ROLLBACK_SEGS WHERE STATUS LIKE 'PART%' or status like 'NEED%';

SEGMENT_NAME                   STATUS

------------------------------ ----------------

_SYSSMU17$                     NEEDS RECOVERY

SQL>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

UNDOTBS1                       OFFLINE

SQL>alter tablespace UNDOTBS1 ONLINE;

Tablespace altered.

SQL>SELECT SEGMENT_NAME,STATUS  FROM DBA_ROLLBACK_SEGS WHERE STATUS LIKE 'PART%' or status like 'NEED%';

 

 SEGMENT_NAME                   STATUS

------------------------------ ----------------

_SYSSMU17$                     NEEDS RECOVERY

 

SQL>alter rollback segment "_SYSSMU17$" online;

 

Rollback segment altered.

 

SQL>SELECT SEGMENT_NAME,STATUS  FROM DBA_ROLLBACK_SEGS WHERE STATUS LIKE 'PART%' or status like 'NEED%';

 

no rows selected