Undo Tbs Increases and Doesn't Shrink or Gone to Recover State

drop Tablespace UNDOTBS;

drop Tablespace UNDOTBS

*
ERROR at line 1:

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

       1)Create pfile if you started with database with spfile.

SQL>Create PFILE from SPFILE;

        2)Edit pfile and set undo management to manual.

undo_management = manual

       3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:

_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)

       4)Mount the database using new pfile.

Startup mount pfile='fullpath to pfile'

      5)Drop the datafiles,

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

     6)Open the database.

sql>Alter Database Open;

       7)Drop the undo segments,

sql>Drop Rollback Segment "_SYSSMU1$";

......

       8)Drop Old undo tablespace.

sql>Drop Tablespace old_tablespace_name Including Contents;

       9)Add the new undo tablespace.

      10) Shutdown Immediate;

      11) 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.

      12) Startup the Database.