Thursday, September 2, 2010

ORA-01548: active rollback segment '_SYSSMU39_115105166$' found

I tried to drop a undo tablespace that contains active rollback segments and got ORA-01548 error.

To resolve this problem follow below steps:
1. SQL>Create PFILE from SPFILE;

2. Edit pfile and set undo management to manual.
undo_management = manual

3. Enter the rollback segment using below parameter:

_offline_rollback_segments=('_SYSSMU39_115105166$')

4. SQL>startup mount pfile='\....\pfile'

5. alter Database datafile 'filename' offline drop; [if the datafile is corroupted]

6. SQL>alter Database Open;

7. SQL>drop Rollback Segment "_SYSSMU39_115105166$";

8. SQL>Drop Tablespace old__undo_tablespace_name Including Contents and datafiles;

9. create new undo tablespace.

10. Shutdown Immediate;

11. Change the pfile parameters
Undo_management = AUTO
parameter Undo_tablespace=new_undo_tablespace_name
and remove the _offline_rollback_segments parameter

12. Startup the Database

http://www.oracle-base.com/forums/viewtopic.php?f=1&t=9775&p=20714

1 comment:

  1. Great! This worked a treat. Thanks very much, good tip.

    ReplyDelete