Identifying Undo Tablespace Issues

When an Oracle database's undo tablespace consistently exceeds 85% utilization despite repeated expansions, it raises questions about actual space requirements and proper configuraton. Key metrics to examine:

SELECT a.tablespace_name,
       ROUND(a.bytes/1024/1024/1024, 0) "total_GB",
       ROUND((a.bytes-b.bytes)/1024/1024/1024, 0) "used_GB",
       ROUND(b.bytes/1024/1024/1024, 0) "free_GB",
       ROUND(((a.bytes-b.bytes)/a.bytes)*100, 2) "pct_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_data_files
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_free_space
      GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name LIKE 'UNDO%'
ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC;

Key Undo Configuration Parameters

Critical parameters controlling undo behavior:

SHOW PARAMETER undo

NAME                 TYPE        VALUE
-------------------  ----------  ---------
undo_management      string      AUTO
undo_retention       integer     900
undo_tablespace      string      UNDOTBS1

Understanding Undo Extent States

Undo extents transition through three states:

1. ACTIVE - Currently in use by transactions
2. UNEXPIRED - Retained per undo_retention but not active
3. EXPIRED - Available for reuse

SELECT tablespace_name, status, 
       ROUND(SUM(bytes/1024/1024/1024),2) "size_GB"
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY 1, 2;

Calculating Optimal Undo Size

To determinne appropriate undo sizing:

SELECT ur undo_retention_sec,
       dbs block_size_bytes,
       ROUND(((ur*(ups*dbs))+(dbs*24))/1024/1024,2) "required_MB"
FROM (SELECT value ur FROM v$parameter WHERE name='undo_retention'),
     (SELECT (SUM(undoblks)/SUM(((end_time-begin_time)*86400))) ups
      FROM v$undostat),
     (SELECT value dbs FROM v$parameter WHERE name='db_block_size');

For peak usage analysis:

SELECT ur undo_retention_sec,
       dbs block_size_bytes,
       ROUND(((ur*(ups*dbs))+(dbs*24))/1024/1024,2) "peak_MB"
FROM (SELECT value ur FROM v$parameter WHERE name='undo_retention'),
     (SELECT (undoblks/((end_time-begin_time)*86400)) ups
      FROM v$undostat
      WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
     (SELECT value dbs FROM v$parameter WHERE name='db_block_size');

Testing Undo Behavior

To validate undo space recycling:

-- Set short retention for testing
ALTER SYSTEM SET undo_retention=10 SCOPE=BOTH;

-- Generate undo activity
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO test_table VALUES(i,MOD(i,10));
  END LOOP;
  COMMIT;
END;
/

-- Monitor state transitions
SELECT status, ROUND(SUM(bytes)/1024/1024,2) "size_MB"
FROM dba_undo_extents
GROUP BY status;

Key Recommendations

1. Size undo tablespace to accommodate:

   • Peak concurrent transaction volume
   • Required retention period (undo_retention)
   • All ACTIVE + UNEXPIRED extents

2. For critical systems, consider:

   ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
   

   This ensures UNEXPIRED extents are preserved for the full retention period.

3. Regular monitoring through:

   SELECT * FROM v$undostat;
   SELECT * FROM dba_hist_undostat;