Converting from Rollback to Undo

This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.


1. Identify the rollback segments and tablespaces to be removed:

select segment_name, tablespace_name, status

from dba_rollback_segs and tablespace name != 'SYSTEM';

2. Produce offline and drop statements for each rollback segment:

select 'alter rollback segment ' || segment_name || ' offline;' from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace name != 'SYSTEM'

select 'drop rollback segment ' || segment_name || ';'

from dba_rollback_segs

where tablespace_name = '&TS'

and tablespace name != 'SYSTEM'

Review the output and then cut and paste it back into sqlplus.


3. Make a note of the old tablespace's size and location, then drop it. 

drop tablespace including contents and datafiles;

4. Create a new 'undo' tablespace in place of the old one.

create undo tablespace undo 

datafile '' size 

extent management local

5. Update the initialisation parameters

If you are using a spfile (and you should be!) run the following commands:

alter system reset rollback_segments scope=spfile sid='*'

alter system set undo_management=auto scope=spfile

alter system set undo_tablespace=undo scope=spfile

If you are still using a pfile (init.ora) then do the following:

Remove the following settings:

rollback_segments=...

Add/alter these two lines:

undo_management=auto

undo_tablespace=undo

6. Restart the instance

Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.
Restart the instance:


shutdown immediate

startup


Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1. This is an indictaion that the instance is now using auto undo. 
Alter undo retentionalter system set undo_retention=500 scope=memory; 

What's in undo 

select tablespace_name, status, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name, status