Converting from Rollback to Undo

From :

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
    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:


    Add/alter these two lines:

  • 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

    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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: