Category: Troubleshooting

Introduction

A production alert indicating UNDO tablespace usage at ~99% is typically treated as a critical issue. However, in Oracle Multitenant environments with local UNDO enabled, this metric can be misleading when evaluated using traditional tablespace monitoring techniques.

This article presents a real-world scenario involving the PDB-level UNDO tablespace APPS_UNDOTS1, where utilization reached ~99%, yet no actual resource contention existed.

---

The Incident

An alert was triggered showing:

Tablespace              TOTAL_MB     USED_MB     FREE_MB PCT_USED
-------------------- ----------- ----------- ----------- --------
APPS_UNDOTS1           28,863.25   28,681.56      181.69    99.37

At first glance, this indicated:

• UNDO nearly full
• Minimal free space
• Risk of ORA-30036 / ORA-01555

---

Environment Context

This issue occurred in a Multitenant environment with Local UNDO enabled:

• UNDO tablespace: APPS_UNDOTS1
• Scope: PDB-level
• Each PDB maintains its own UNDO

UNDO behavior must always be analyzed at the PDB level, not just at the CDB level.

---

Investigation Approach

Rather than relying on % used, a state-based analysis was performed.

---

Step 1: Active Transactions

SELECT COUNT(*) FROM v$transaction;

Observation:

• Minimal active transactions
• No ongoing workload pressure

---

Step 2: UNDO Extent Analysis

SELECT status, SUM(bytes)/1024/1024 MB
FROM dba_undo_extents
GROUP BY status;

---

Key Observation

STATUS        MB         PCT
-----------   --------   -------
ACTIVE        ~3 MB      ~0.01%
UNEXPIRED     ~51 MB     ~0.18%
EXPIRED       ~28 GB     ~99.81%

---

💡 Critical Insight

~99.81% of UNDO (APPS_UNDOTS1) was EXPIRED and fully reusable

This means:

• No real space pressure
• No transaction risk
• Tablespace is effectively available

---

Understanding UNDO Behavior

UNDO extents follow a lifecycle:

ACTIVE → UNEXPIRED → EXPIRED → REUSED

State	Description	Reusable
ACTIVE	Used by active transactions	❌
UNEXPIRED	Retained for consistency	⚠️
EXPIRED	No longer needed	✅

UNDO extents are not physically freed but logically reused by Oracle based on demand.

---

Why the Alert Was Misleading

The alert was based on:

SELECT * FROM dba_free_space;

Limitation

• Shows only physically free space
• Does not reflect reusable UNDO
• Ignores Oracle’s internal reuse mechanism

---

Root Cause

A long-running concurrent request:

• Ran for ~30 hours
• Performed heavy DELETE operations
• Generated significant UNDO

During execution:

• UNDO reached ~99% → real pressure

After completion:

• UNDO became EXPIRED
• Space became reusable
• Alert persisted → false positive

---

When UNDO Is Actually a Problem

ACTIVE      > 20–30%
UNEXPIRED   very high
EXPIRED     very low

UNDO becomes a real issue only when both EXPIRED and UNEXPIRED extents are exhausted and no reusable space remains.

---

Recommended Monitoring Approach

WITH undo AS (
    SELECT status, SUM(bytes)/1024/1024 mb
    FROM dba_undo_extents
    GROUP BY status
),
total AS (
    SELECT SUM(mb) total_mb FROM undo
)
SELECT 
    u.status,
    ROUND(u.mb,2) AS mb,
    ROUND((u.mb / t.total_mb) * 100, 2) AS pct
FROM undo u, total t;

---

Improved Alert Logic

• ACTIVE > 20% → Critical
• UNEXPIRED > 80% → Warning
• Otherwise → Normal

---

Key Takeaways

• UNDO is state-driven, not space-driven
• EXPIRED undo = reusable capacity
• % used is misleading
• Always analyze at PDB level in multitenant setups
• Monitoring must align with Oracle internals

---

Conclusion

UNDO tablespace monitoring must evolve from:

❌ Static space-based metrics
➡️
✅ Dynamic lifecycle-based analysis

A tablespace showing 99% utilization can still be completely healthy if most of its extents are reusable.

---

Level Insight

“UNDO is not a storage problem — it is a lifecycle problem. The difference between false alarms and accurate diagnosis lies in understanding how Oracle transitions undo extents.”

---

Final Thought

👉 Don’t ask:
“How full is UNDO?”

👉 Ask:
“How much of it is actually in use?”

---

Author

Syed Anwar Ahmed
Oracle Apps DBA | Oracle EBS | Performance & Troubleshooting