Wednesday, January 7, 2009

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

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.

No comments:

Post a Comment