Some thing about Undo

Oracle Database creates and manages 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 Oracle Flashback Query

Recover from logical corruptions using Oracle Flashback features

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 data files. 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.

• There are two methods for managing undo data:

– Automatic Undo Management

– Manual Undo Management

Managing Undo Data

Automatic Undo Management

The Oracle server automatically manages the creation, allocation, and tuning of undo segments.

Manual Undo Management

You manually manage the creation, allocation, and tuning of undo segments. It was the only

method available prior to Oracle9i.

Undo Segment

An undo segment is used to save the old value (undo data) when a process changes data in a

database. It stores the location of the data and the data as it existed before being modified.

The header of an undo segment contains a transaction table where information about the

current transactions using the undo segment is stored.

A serial transaction uses only one undo segment to store all of its undo data.

Many concurrent transactions can write to one undo segment.

Undo Segments: Purpose

Transaction Rollback

When a transaction modifies a row in a table, the old image of the modified columns (undo

data) is saved in the undo segment. If the transaction is rolled back, the Oracle server restores

the original values by writing the values in the undo segment back to the row.

Transaction Recovery

If the instance fails while transactions are in progress, the Oracle server needs to undo any

uncommitted changes when the database is opened again. This rollback is part of transaction

recovery. Recovery is possible only because changes made to the undo segment are also

protected by the online redo log files.

Read Consistency

While transactions are in progress, other users in the database should not see any uncommitted

changes made by these transactions. In addition, a statement should not see any changes that

were committed after the statement begins execution. The old values (undo data) in the undo

segments are also used to provide the readers a consistent image for a given statement.