🧩 How to Monitor Undo Tablespace Usage in Oracle Database

🧩 Introduction

The Undo Tablespace is essential in Oracle databases for managing transactions, supporting read consistency, and providing rollback for failed operations. If the undo tablespace runs out of space, it can cause transaction failures and errors like ORA-30036: unable to extend segment by.... As a DBA, monitoring undo usage is critical to ensure smooth database operations.

Step 1: Check Current Undo Tablespace

You can find the active undo tablespace using:

SHOW PARAMETER undo_tablespace;

➡️ This tells you which undo tablespace is currently in use.

Step 2: Monitor Undo Tablespace Usage

Run the following query to see how much space is used:

SELECT a.tablespace_name,

       ROUND((b.bytes_used / b.bytes_free + b.bytes_used) * 100, 2) AS used_percent

FROM dba_tablespaces a

JOIN v$tablespace c ON a.tablespace_name = c.name

JOIN v$temp_space_header b ON c.ts# = b.tablespace_id;

➡️ This helps you track space usage in the undo tablespace.

Step 3: Monitor Undo Extents

You can also check how undo extents are being used:

SELECT tablespace_name,

       status,

       COUNT(*) AS extent_count

FROM dba_undo_extents

GROUP BY tablespace_name, status;

➡️ This shows extents categorized as ACTIVE, EXPIRED, or UNEXPIRED.

ACTIVE → Currently in use by running transactions

EXPIRED → No longer needed, can be reused

UNEXPIRED → Needed for read consistency

Step 4: Identify Undo Usage by Session

To find sessions consuming undo space:

SELECT s.sid, s.serial#, u.tablespace, u.blocks * TO_NUMBER(p.value)/1024 AS undo_kb

FROM v$session s

JOIN v$transaction t ON s.saddr = t.ses_addr

JOIN v$rollstat r ON t.xidusn = r.usn

JOIN v$rollname n ON r.usn = n.usn

JOIN dba_tablespaces u ON n.name = u.tablespace_name

JOIN v$parameter p ON p.name = 'db_block_size';

➡️ Useful for identifying sessions with high undo usage.

Step 5: Consider Adding Space if Needed

If undo tablespace is consistently over 80% used, add space:

ALTER DATABASE DATAFILE '/u01/oradata/ORA/undotbs01.dbf' RESIZE 1G;

➡️ Or add a new datafile to increase capacity.

Conclusion

Monitoring undo tablespace usage is a vital DBA task to avoid transaction failures and maintain database stability. Regular checks on usage, extents, and session-level consumption help ensure that undo space is well-managed.