There are ACTIVE and PARTIAL AVAILABLE segments in UNDO:

select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′

SEGMENT_NAME TABLESPACE_NAME STATUS

—————————— —————————— —————-

_SYSSMU10_820739558$ UNDOTBS1 OFFLINE

_SYSSMU9_2448906239$ UNDOTBS1 OFFLINE

_SYSSMU8_3066916762$ UNDOTBS1 OFFLINE

_SYSSMU7_892861194$ UNDOTBS1 OFFLINE

_SYSSMU6_1956589931$ UNDOTBS1 OFFLINE

_SYSSMU5_2919322705$ UNDOTBS1 OFFLINE

_SYSSMU4_3876247569$ UNDOTBS1 OFFLINE

_SYSSMU3_4245574747$ UNDOTBS1 OFFLINE

for eaxmple :_SYSSMU2_6654314$ UNDOTBS1 PARTLY AVAILABLE

Check what sessions hold them:

select SID, substr(username,1,10) username,serial#,segment_name from v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and xidusn=segment_id;

and KILL

After that ACTIVE segments should be gone.

2) set a parameter including all active rollback segments in init.ora file

_offline_rollback_segments=(_SYSSMU2_6654314$,…..)

3) shutdown database

4) Mount the database using pfile

SQL> startup mount pfile=’init.ora’

5) offline undo datafile for drop

SQL> alter database datafile ‘/app/oracle/oradata/orcl/undotbs1.dbf’ offline drop;

6) open database

SQL> alter database open;

7) add new UNDO tablespace

SQL> alter system set undo_tablespace=undotbs2;

8) drop the undo segment or whole tablespace;

SQL> drop rollback segment “_SYSSMU2_6654314$”;

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

9) remove the _offline_rollback_segments parameter from pfile;