How to Monitor and Tune Oracle Undo Tablespace (Avoid ORA-01555 & ORA-30036)

Oracle Undo Tablespace is one of the most critical — yet often neglected — components of Oracle Database.
It plays a key role in transaction consistency, rollback operations, and read consistency across sessions.

When undo space runs out or is poorly tuned, Oracle starts throwing errors like:

ORA-01555: Snapshot too old: rollback segment number with name "" too small
ORA-30036: Unable to extend segment by X in undo tablespace UNDO_TBS1

These errors can interrupt long-running queries and batch jobs.
Let’s walk through how to monitor, manage, and tune undo tablespace usage effectively — and eliminate these errors for good.

---

What Is Undo Tablespace?

Undo tablespace stores information needed to rollback transactions and maintain read consistency.

For example, if a user queries a table while another updates it, Oracle uses undo records to reconstruct the original data so both see consistent results.

Oracle automatically manages undo when:

• The instance is running in Automatic Undo Management (AUM) mode.
• Parameter undo_management=AUTO is set.

---

Undo Parameters Every DBA Should Know

Parameter	Description
undo_management	Must be set to AUTO for automatic undo.
undo_tablespace	The active undo tablespace in use.
undo_retention	Time (in seconds) Oracle retains undo data.
undo_retention_guarantee	Ensures undo data isn’t overwritten before retention expires.

Example:

SHOW PARAMETER undo;

Output:

undo_management          AUTO
undo_tablespace          UNDOTBS1
undo_retention           900

---

Step 1: Monitor Undo Usage

Use the dynamic views to check undo utilization:

SELECT a.tablespace_name, a.file_id, a.bytes/1024/1024 AS size_mb,
       (a.bytes - b.bytes_used)/1024/1024 AS free_mb
FROM dba_data_files a,
     (SELECT tablespace_name, file_id, SUM(bytes) AS bytes_used
      FROM dba_undo_extents WHERE status='ACTIVE' GROUP BY tablespace_name, file_id) b
WHERE a.file_id=b.file_id;

Or a simpler version:

SELECT tablespace_name, status, COUNT(*)
FROM dba_undo_extents
GROUP BY tablespace_name, status;

Status meanings:

• ACTIVE – in use by running transactions.
• UNEXPIRED – committed but still retained for read consistency.
• EXPIRED – available for reuse.

---

Step 2: Check Undo Tablespace Growth Trend

SELECT to_char(begin_time,'HH24:MI') time, tablespace_name, used_undo_space/1024/1024 used_mb
FROM v$undostat
ORDER BY begin_time DESC;

This view helps visualize how undo grows during peak activity.

💡 Tip: Monitor MAXQUERYLEN — the longest-running query time.
Your undo retention should be greater than MAXQUERYLEN.

---

Step 3: Tuning Undo Retention

Set undo retention based on your workload.

ALTER SYSTEM SET undo_retention=1800;

For OLTP systems, 900–1800 seconds (15–30 mins) is usually fine.
For reporting or ETL-heavy systems, use 3600 seconds or more.

To guarantee retention (use with care):

ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' AUTOEXTEND ON;
ALTER SYSTEM SET undo_retention_guarantee=TRUE;

This prevents Oracle from overwriting undo before the retention period expires — at the cost of using more space.

---

Step 4: Fixing ORA-01555 and ORA-30036

---

✅ Fix ORA-01555 (Snapshot Too Old)

Causes:

• Undo overwritten before a long-running query finishes.
• Undo tablespace too small.
• Insufficient retention time.

Solutions:

Increase undo_retention:

ALTER SYSTEM SET undo_retention=3600;

Add space to undo tablespace:

ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' RESIZE 5G;

Avoid unnecessary commits inside loops — they break undo chains.

---

✅ Fix ORA-30036 (Unable to Extend Segment)

Causes:

• Undo tablespace full.
• No autoextend on datafiles.

Solutions:

Enable autoextend:

ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

Add another datafile:

ALTER DATABASE ADD DATAFILE '/u02/oradata/UNDOTBS02.dbf' SIZE 3G AUTOEXTEND ON;

1. Monitor v$undostat regularly to predict peak undo usage.

---

Step 5: Switching Undo Tablespace

If your undo tablespace becomes fragmented or too large:

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u02/oradata/UNDOTBS02.dbf' SIZE 3G AUTOEXTEND ON;
ALTER SYSTEM SET undo_tablespace=UNDOTBS2;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Always ensure no active transactions before dropping the old one.

---

Step 6: Using AWR to Analyze Undo Usage

Run AWR or ASH reports and check:

• “Undo Segment Summary”
• “Undo Space Usage History”

These help identify queries consuming excessive undo.

You can also monitor in near real-time:

SELECT * FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 10 ROWS ONLY;

---

Real-World Example

A financial batch system frequently failed with:

ORA-01555: Snapshot too old
ORA-30036: Unable to extend segment by 128 in undo tablespace UNDOTBS1

Diagnosis:
Undo tablespace was 4GB, undo retention only 900 seconds, but batch jobs ran 45 minutes.

Fix:

1. Increased undo tablespace to 15GB.
2. Set undo_retention to 4000 seconds.
3. Added autoextend.

✅ Result: Batch jobs completed successfully, and no undo-related errors occurred afterward.

---

Best Practices for Undo Management

1. Enable Automatic Undo Management (AUM) always.
2. Size undo tablespace for longest query duration.
3. Monitor v$undostat daily on heavy systems.
4. Use autoextend to avoid ORA-30036.
5. Don’t commit too frequently in loops.
6. Match undo_retention to actual workload patterns.
7. Move undo to fast storage (SSD/NVMe) for better performance.

---

Related Articles

• ORA-01652: Unable to Extend Temp Segment in Temporary Tablespace – Fix Guide
• ORA-04030: Out of Process Memory in PGA – Oracle Memory Fix Guide
• ORA-01578: ORACLE Data Block Corruption Detected – Fix and Recovery Guide

---

Final Thoughts

The undo tablespace is your database’s safety net for transaction recovery and consistency.
Properly monitoring and tuning it ensures you’ll never lose data due to undo exhaustion.

Proactive undo management is not just good practice — it’s a hallmark of a skilled DBA.