Enabling System Managed Undo (SMU)

As of 9iR2, Oracle Applications Release 11i supports only the use of system managed undo (SMU). SMU is more efficient than traditional rollback segments and reduces the possibilities of “snapshot too old errors.” The following steps should be used to enable system managed undo.

Step 1:
Capture the tablespace names being used for the existing rollback segments from dba_rollback_segs. Capture the datafiles and sizes being used for the rollback tablespaces from dba_data_files.

SQL> select segment_name, tablespace_name from dba_rollback_segs;

SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name = '';
where is the tablespace name used by a rollback segment.

Step 2:
Drop all the private and public rollback segments except for the SYSTEM rollback segment.

SQL> alter rollback segment offline;
SQL> drop rollback segment ;

Step 3:
Drop the corresponding tablespace(s) where the rollback segments reside.

SQL> alter tablespace offline;
SQL> drop tablespace ;

Step 4:
Create a new System Managed Undo tablespace per instance as follows:

create undo tablespace APPS_UNDOTS1 datafile '' size reuse extent management local ;

For the path and the size values, you should reuse the same files and sizes which were used in the tablespaces which were dropped in step 2. The sizes and file names were captured in step 1.

Step 5:
Set the following database initialization parameters:

undo_management = AUTO
undo_tablespace = APPS_UNDOTS1

Refer to the sizing table (Database Initialization Parameters Sizing section) for the appropriate setting of undo_retention.

Step 6:
Remove the database initialization parameter rollback_segments.

Step 7:
Restart the database so that the system managed undo takes effect.