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

Posted by Mawahid

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

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.


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:


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.

This entry was posted on Tuesday, January 11, 2011 at 8:40 AM . You can follow any responses to this entry through the comments feed .


Post a Comment