What is the difference between Rollback and Undo Tablespace? OTN Forum By user user503050

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.


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 statement 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 you when you 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 (10g and up) that provides information on how to size new undo tablespace based on the configuration and usage of the rollback segments in the system.

utbsiz_in_MB NUMBER;

DATAFILE ‘/oradata/dbf/undotbs_1.dbf’

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.


Francisco Munoz Alvarez

2 thoughts on “What is the difference between Rollback and Undo Tablespace? OTN Forum By user user503050

  1. Pingback: ora-click.com

  2. Muy bonito, bastante claro.

    Hay algún consejo para optimizar el tablespace de Undo? Tamaño de bloque en especial? Configuración de discos? Existe la posibilidad de que las operaciones a nivel de base, presente algún tipo de lentitud asociada con esta tablespace, es decir, puede llegar a convertirse en un “Wait Event”?


Leave a Reply