ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

While trying to recreate undotbs to flip with a new one , ran into this unseen error.

Created a new UNDO, made it the default UNDO for the database, now while trying to drop the old undo it fails with error Ora-01548 .

SQL> drop Tablespace UNDOTBS1;

drop Tablespace UNDOTBS

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping

tablespace

Cause of The Problem

An attempt was made to drop a tablespace that contains active rollback segments.

Solution:

 Following steps were performed:

1.    Figure out the rollback segments still occupying UNDOTBS1

SQL> select * from v$rollname ;
USN NAME
---------- ------------------------------
0 SYSTEM
18 _SYSSMU18_650722988$
19 _SYSSMU19_176786070$
20 _SYSSMU20_2971238361$
21 _SYSSMU21_1337078425$
22 _SYSSMU22_378998127$
23 _SYSSMU23_2828850578$
24 _SYSSMU24_3342375805$
25 _SYSSMU25_1071547613$
26 _SYSSMU26_357778769$
27 _SYSSMU27_3256159541$

2.    Create pfile if you started with database with spfile.

SQL>Create PFILE from SPFILE;

3.    Edit pfile and set undo management to manual.

undo_management = manual

4.    Put the entries of the undo segments in the pfile by using the following statement in the pfile:

_offline_rollback_segments=(_SYSSMU18_650722988$,_SYSSMU19_176786070$,_SYSSMU20_2971238361$,_SYSSMU21_1337078425$,_SYSSMU22_378998127$,_SYSSMU23_2828850578$,_SYSSMU24_3342375805$,_SYSSMU25_1071547613$,_SYSSMU26_357778769$,_SYSSMU27_3256159541$)

5.    Mount the database using new pfile.

Startup mount pfile='/$ORACLE_HOME/dbs/'

6.    Drop the datafiles,

sql>Alter Database datafile '&filename' offline drop;

7.    Open the database.

sql>Alter Database Open;

8.    Drop the undo segments,

sql>Drop Rollback Segment "_SYSSMU1$";

......

9.    Drop Old undo tablespace.

sql>Drop Tablespace old_tablespace_name Including Contents;

10. Add the new undo tablespace.

11. Shutdown Immediate;

12. Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.

13. Startup the Database.