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

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 .

0 comments

Post a Comment