UNDO_RETENTION - Prevent ORA-01555 Snapshot Too Old

UNDO_RETENTION

Overview

UNDO_RETENTION specifies the minimum number of seconds that Oracle should attempt to retain committed undo data before overwriting it. This retained undo is what Oracle uses to satisfy read-consistent queries: when a long-running SELECT needs to read a block as it looked at query-start, Oracle reconstructs the old version from undo data. If that undo has already been overwritten, the query fails with ORA-01555: snapshot too old.

The parameter is a target, not a guarantee. By default, Oracle may still overwrite unexpired undo if the tablespace has no free space. To make retention a hard guarantee, enable RETENTION GUARANTEE on the undo tablespace (Oracle 10g+). The setting must be large enough to cover the longest expected query execution time in your environment.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 900 (15 minutes) Valid Range: 0 to 2^31 − 1 seconds (no practical upper limit, but tablespace must be sized accordingly) Available Since: Oracle 9i Modifiable: Yes — ALTER SYSTEM (effective immediately, no restart required) PDB Modifiable: Yes — settable at PDB level in Oracle 12.2+ with local undo

Configuration

Viewing Current Value

-- Current in-memory value
SELECT name,
       value,
       description,
       isdefault
FROM   v$parameter
WHERE  name = 'undo_retention';

-- SPFILE value (survives restarts)
SELECT name, value
FROM   v$spparameter
WHERE  name = 'undo_retention';

-- Check if RETENTION GUARANTEE is active on the undo tablespace
SELECT tablespace_name,
       retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO';
-- RETENTION column: NOGUARANTEE (default) or GUARANTEE

Setting the Parameter

-- Set undo retention to 1 hour (3600 seconds)
ALTER SYSTEM SET undo_retention = 3600 SCOPE = BOTH;

-- Set undo retention to 4 hours for a reporting environment with long queries
ALTER SYSTEM SET undo_retention = 14400 SCOPE = BOTH;

-- Verify the change
SELECT name, value FROM v$parameter WHERE name = 'undo_retention';

Enabling Retention Guarantee

-- Force Oracle to never overwrite unexpired undo (requires adequately sized tablespace)
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- To revert to the default (allow overwrite when space is tight)
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

-- Confirm the retention mode
SELECT tablespace_name, retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO';

Tuning Guidance

Recommended Values

Environment	Recommended Value	Rationale
OLTP, short transactions	900 – 1800 s	Default sufficient; queries are fast
Mixed OLTP + reporting	3600 – 7200 s	Accommodate ad-hoc reports (up to 2 hours)
Data Warehouse / batch	14400 – 28800 s	Long ETL and analytical queries
Flashback Query enabled	Match flashback window	Must cover the desired flashback interval
Active Data Guard reads	Match longest standby query	Undo must survive replication lag + query time

How to Size Using V$UNDOSTAT

V$UNDOSTAT captures a 10-minute statistical sample. The MAXQUERYLEN column records the longest running query in each interval — that value is the minimum safe UNDO_RETENTION.

-- Find the maximum query length over the past 7 days
SELECT MAX(maxquerylen)                          AS max_query_seconds,
       MAX(maxquerylen) / 60                     AS max_query_minutes,
       MAX(maxquerylen) / 3600                   AS max_query_hours,
       ROUND(MAX(maxquerylen) * 1.25)            AS recommended_retention
FROM   v$undostat
WHERE  begin_time >= sysdate - 7;

-- Detailed view: query length by day to spot batch windows
SELECT trunc(begin_time)                        AS day,
       MAX(maxquerylen)                         AS max_query_secs,
       SUM(undoblks)                            AS total_undo_blocks,
       SUM(txncount)                            AS total_txns,
       SUM(ssolderrcnt)                         AS ora_01555_errors
FROM   v$undostat
WHERE  begin_time >= sysdate - 7
GROUP BY trunc(begin_time)
ORDER BY day DESC;

Add a 25% buffer to the maximum query length as the recommended retention value. If ORA-01555 errors appear in the SSOLDERRCNT column, retention is too low or the undo tablespace is too small for the current retention target.

Sizing the Undo Tablespace for a Given Retention

Once you know the target retention, calculate the required tablespace size:

-- Required undo size = UNDO_RETENTION * undo blocks per second * block size
SELECT p_ret.value                                           AS retention_secs,
       p_blk.value                                          AS block_size_bytes,
       ROUND(AVG(us.undoblks / 600))                       AS avg_undo_blks_per_sec,
       ROUND(
         p_ret.value
         * AVG(us.undoblks / 600)
         * p_blk.value
         / (1024*1024*1024),
         2
       )                                                    AS required_gb
FROM   v$undostat us,
       (SELECT value FROM v$parameter WHERE name = 'undo_retention') p_ret,
       (SELECT value FROM v$parameter WHERE name = 'db_block_size')  p_blk
WHERE  us.begin_time >= sysdate - 1
GROUP BY p_ret.value, p_blk.value;

Monitoring

-- Check for ORA-01555 occurrences and unexpired undo pressure
SELECT begin_time,
       end_time,
       undoblks,
       txncount,
       maxquerylen,
       maxconcurrency,
       ssolderrcnt      AS ora_01555_count,
       nospaceerrcnt    AS no_space_count,
       activeblks,
       unexpiredblks,
       expiredblks
FROM   v$undostat
ORDER BY begin_time DESC
FETCH FIRST 48 ROWS ONLY;

-- Current undo extent status breakdown
SELECT status,
       COUNT(*)                           AS extents,
       ROUND(SUM(bytes)/1024/1024, 1)    AS total_mb
FROM   dba_undo_extents
GROUP BY status
ORDER BY status;

-- Alert if ORA-01555 errors have occurred in the last hour
SELECT SUM(ssolderrcnt) AS recent_01555_errors
FROM   v$undostat
WHERE  begin_time >= sysdate - 1/24;

Common Issues

Issue 1: ORA-01555 despite a large UNDO_RETENTION value

Symptom: Queries fail with ORA-01555 even though UNDO_RETENTION is set to a large value.

Cause: UNDO_RETENTION is a best-effort target. When the undo tablespace runs out of free space, Oracle overwrites unexpired undo to make room for active transactions — ignoring the retention target. This is the default RETENTION NOGUARANTEE behaviour.

Resolution:

-- Step 1: Confirm that undo is being overwritten (nospaceerrcnt will be > 0)
SELECT SUM(nospaceerrcnt) AS space_pressure FROM v$undostat WHERE begin_time >= sysdate - 1;

-- Step 2: Either add space or enable GUARANTEE
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- Step 3: If GUARANTEE causes ORA-30036 (space full), add a datafile
ALTER TABLESPACE undotbs1
  ADD DATAFILE '/u01/oradata/PROD/undotbs01b.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

Issue 2: Setting UNDO_RETENTION too high causes the tablespace to fill

Symptom: After increasing UNDO_RETENTION, the undo tablespace grows quickly and DML begins to fail with ORA-30036.

Cause: A higher retention value means Oracle retains more unexpired undo blocks. If the tablespace is not sized to hold that volume, it exhausts available space.

Resolution: Calculate the required tablespace size using the formula above before raising retention. Either pre-allocate sufficient space or keep RETENTION NOGUARANTEE and accept that retention is best-effort.

Issue 3: Flashback Query fails with ORA-01555

Symptom: SELECT ... AS OF TIMESTAMP or Flashback Table operations fail.

Cause: The Flashback query requires undo data older than the current UNDO_RETENTION interval.