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
Great! This worked a treat. Thanks very much, good tip.
ReplyDelete