What is Oracle UNDO? Complete Explanation with Diagrams: Mechanism, Purpose, and Usage Monitoring

UNDO is a vital mechanism in Oracle Database that allows for transaction rollback, read consistency, and flashback operations. In this article, we will provide a comprehensive and visual explanation of how UNDO works, its purpose, and how to monitor its usage.

---

What is UNDO?

UNDO stores the before image of data that was modified by DML statements like INSERT, UPDATE, and DELETE. It allows Oracle to reverse changes made by a transaction that is rolled back.

┌──────────────────────────┐
│ 　　UPDATE emp SET job='CLERK' WHERE empno=7369　　│
└────────────┬─────────────┘
             ▼
     ┌────────────────┐     ┌─────────────────┐
     │　　　　  Before Image 　　　　 │ →  │　　　　  After Image   　　　　  │
     │ 　　　　job='SALESMAN' 　　　　│     │　　　　 job='CLERK'  　　　　    │
     └────────────────┘     └─────────────────┘

---

Purpose of UNDO

🔁 Transaction Rollback

If a user issues ROLLBACK, Oracle uses the UNDO data to restore the original values.

🔍 Read Consistency

Oracle ensures readers don’t see uncommitted changes from other sessions by using UNDO data to present a consistent view of the data.

⏪ Flashback Features

UNDO enables features such as FLASHBACK QUERY, allowing you to view past data by reconstructing it from UNDO segments.

---

How UNDO Works Internally

1. When a DML operation occurs, the before image is written to an undo segment.
2. This data is stored in the UNDO tablespace.
3. If rollback is issued or a query requires old data, Oracle accesses the undo segment.

┌─────────────┐
│ 　　Undo Tablespace　　　│
├─────────────┤
│ 　　Segment 1    　　 　 │
│ 　　Segment 2    　　　  │
└─────────────┘

---

How to Check UNDO Usage

You can estimate how much UNDO is currently being used with the following SQL:

SELECT t.used_ublk * TO_NUMBER(p.value) / 1024 / 1024 AS undo_used_mb
FROM v$transaction t, v$parameter p
WHERE p.name = 'db_block_size';

Or if using Oracle 12c and later, UNDO usage per session can be retrieved:

SELECT sid, used_ublk, used_urec
FROM v$transaction;

---

Example Table (EMP) for Testing

CREATE TABLE emp (
  empno NUMBER PRIMARY KEY,
  ename VARCHAR2(30),
  job   VARCHAR2(30)
);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK');
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN');
COMMIT;

Then start a transaction like:

UPDATE emp SET job='MANAGER' WHERE empno=7369;
-- Do not COMMIT or ROLLBACK yet

Now check UNDO usage.

---

When UNDO is Automatically Reused

UNDO segments are automatically reused after the undo retention period expires and the undo data is no longer needed.

You can check this retention with:

SHOW PARAMETER undo_retention

---

Summary

• UNDO stores the old image of data to enable rollback and consistent reads
• Essential for ROLLBACK, read consistency, and flashback queries
• Stored in the UNDO tablespace and managed automatically
• Use v$transaction and v$parameter to monitor usage

---

[reference]
16 Managing Undo