Could not drop undo tablespace fails with error Ora-01548 .

Problem Description

Could not drop undo tablespace fails with error Ora-01548 .

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1186132793$' 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.

Step1: Find all Undo Segments

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

Step2: Create pfile if you started with database with spfile.

SQL>Create PFILE from SPFILE;

Step3: Edit pfile and set undo management to manual.
undo_management = manual

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

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

Step5: Mount the database using new pfile.
Startup mount pfile='fullpath to pfile' 

Step6: Drop the datafiles,
sql>Alter Database datafile '&filename' offline drop;

Step7: Open the database.
sql>Alter Database Open;

Step8: Drop the undo segments,

sql>Drop Rollback Segment "_SYSSMU1$";
......
Step9: Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;

Step10: Add the new undo tablespace.

Step11:  Shutdown Immediate;

Step12:  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.

Step13: Startup the Database.