How Oracle Uses Undo to Provide Consistent Reads and Recover Transactions

Undo in Oracle: Core Functions

Oracle uses undo segments to support three essential mechanisms: consistent reads, transaction rollback, and instance recovery.

Consistent Read Mechanism

When a query starts, the server records the current System Change Number (SCN). Each data block header contains an ITL (Interested Transaction List) slot that stores the SCN of the last modifying transaction for that block. The server compares the block’s ITL SCN with the query SCN:

If the block SCN ≤ query SCN, the block can be read directly.

If the block SCN > query SCN, the block has been modified after the query start; the server follows the ITL pointer to the corresponding undo block, extracts the pre‑change image, and builds a Consistent Read (CR) block that reflects the data as of the query SCN.

SQL Example

SELECT * FROM T;   -- query issued at 09:00, SCN = 9.00

Undo Chain Traversal – Detailed Example

Assume table T contains 10,000 rows and a full scan takes about 15 minutes.

09:00 – User A issues the SELECT. The server records SCN9.00.

09:10 – User B deletes the last row and commits. The ITL slot of the affected block (block N) is updated to SCN9.10 and an undo block Undo_block1 is created containing the row’s original image.

09:11 – User C inserts two new rows into the same block N and commits. The ITL slot becomes SCN9.11. A second undo block Undo_block2 is created; it stores the pre‑insert state (the row that was deleted) and also records the pointer to the previous undo block Undo_block1 together with its SCN 9.10.

During the scan, when the server reaches block N it sees SCN9.11 > SCN9.00 and follows the chain:

Read Undo_block2 (SCN 9.11). Its stored ITL points to Undo_block1 (SCN 9.10).

Read Undo_block1. Its ITL points to the original undo block Undo_block0 with SCN 8.50, which is ≤ SCN9.00.

At this point the chain stops. The server combines the current data in block N with the undo images from Undo_block2 and Undo_block1 to reconstruct a CR block that contains the original 10,000 rows as of 09:00.

Undo Block Structure

Each undo block stores:

The pre‑change row image.

The SCN of the transaction that created the change.

A pointer to the previous undo block (forming a linked list).

Snapshot Too Old (ORA‑1555)

If the undo chain does not contain an entry with an SCN ≤ the query SCN—because older undo blocks have been overwritten by newer transactions—the server cannot reconstruct the required view and raises ORA‑1555 “snapshot too old”.

Rollback of a Transaction

When a ROLLBACK is issued, Oracle locates the undo block(s) referenced by the ITL slot of the modified blocks, extracts the pre‑change images, and writes them back to the data blocks, effectively undoing the DML changes.

Instance Recovery

After an instance crash, the SMON process scans the heads of all undo segments. For each transaction that is neither committed nor rolled back, SMON applies the stored undo information to roll back those incomplete transactions, bringing the database to a consistent state.