Converting from Rollback to Undo

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

  select	segment_name
  ,	tablespace_name
  ,	status
  from	dba_rollback_segs
  where	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, if you are happy with it, cut and paste it back into sqlplus.
  
  
• 3. Make a note of the old tablespace's size and location, then drop it.

  drop tablespace <tablespace_name> including contents and datafiles
  /
  

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

  create undo tablespace undo
  datafile '<path/file>' size <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.