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
% usedis 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
Leave a comment