Problem Description:

Drop undo tablespace fails with error Ora-01548.

SQL> drop Tablespace UNDOTBS;
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 of The Problem:

In order to get rid of this error you have to follow following steps.

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.