Undo vs Rollback Segment

There might be confusion while undo and rollback segment terms are used interchangeably in db world. It is due to the compatibility issue of oracle.

Undo

Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to -

* Roll back transactions when a ROLLBACK statement is issued
* Recover the database
* Provide read consistency
* Analyze data as of an earlier point in time by using Flashback Query

When a ROLLBACK command is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Undo vs Rollback

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.

To see the undo management mode and other undo related information of database-

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Since the advent of Oracle9i, less time consuming and suggested way is — using Automatic Undo Management, in which Oracle Database creates and manages rollback segments (now called "undo segments") in a special-purpose undo tablespace.

Unlike with rollback segments, we don't need to create or manage individual undo segments—Oracle Database does that for us when we create the undo tablespace. All transactions in an instance share a single undo tablespace. Any executing transaction can consume free space in the undo tablespace, and when the transaction completes, its undo space is freed (depending on how it's been sized and a few other factors, like undo retention). Thus, space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than manual management by someone.

Switching Rollback to Undo

1. We have to create an Undo tablespace. Oracle provides a function that provides information on how to size new undo tablespace based on the configuration and usage of the rollback segments in the system.

DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB ;= DBMS_UNDO_ADV.RBU_MIGRATION;
END;
/

CREATE UNDO TABLESPACE UNDOTBS
DATAFILE '/oradata/dbf/undotbs_1.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED RETENTION NOGUARANTEE;

Note: In undo tablespace creation, "SEGMENT SPACE MANAGEMENT AUTO" can not be set

2. Change system parameters

SQL> alter system set undo_retention=900 scope=both;
SQL> alter system set undo_tablespace=UNDOTBS scope=both;
SQL> alter system set undo_management=AUTO scope=spfile;
SQL> shutdown immediate
SQL> startup

UNDO_MANAGEMENT is a static parameter. So database needs to be restarted.

One related article -
http://www.oracle.com/technology/oramag/oracle/05-jul/o45tuning.html