ORA-01555: Snapshot Too Old in Oracle Database – Causes, Diagnosis, Solutions & Prevention

ORA-01555 Snapshot Too Old Oracle Database error

The ORA-01555: snapshot too old error is one of the most common Oracle Database errors encountered during long-running queries, reporting workloads, batch processing, and systems experiencing heavy transactional activity.

The error occurs when Oracle needs an older version of data to provide read consistency, but the required Undo information is no longer available. The problem is therefore not simply "Undo is too small." The real cause may involve query duration, Undo generation rate, SQL execution plans, workload concurrency, commit patterns, or insufficient Undo capacity.

This guide explains how Oracle read consistency works, why ORA-01555 occurs, how to diagnose it using V$UNDOSTAT, how to evaluate UNDO_RETENTION and TUNED_UNDORETENTION, how to size Undo more intelligently, and how to prevent the error in production environments.

Quick DBA Answer

If ORA-01555 occurs, do not immediately increase UNDO_RETENTION. First determine why the required Undo was no longer available.

Identify the affected SQL and its execution duration.
Check V$UNDOSTAT for SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, and TUNED_UNDORETENTION.
Check Undo tablespace size, autoextend configuration, and workload peaks.
Investigate heavy DML and inefficient SQL execution plans.
Reduce unnecessary query execution time.
Increase Undo capacity when workload analysis shows that more space is required.
Adjust UNDO_RETENTION when there is a genuine retention requirement and sufficient Undo capacity.
Use RETENTION GUARANTEE only when its operational consequences are fully understood.

Important: UNDO_RETENTION is not, by itself, a guarantee that unexpired Undo will never be overwritten. With insufficient space, Oracle may reuse unexpired Undo unless retention guarantee is enabled.

What Is ORA-01555?
How Oracle Read Consistency Works
How Undo Is Related to ORA-01555
Common Causes of Snapshot Too Old
ORA-01555 Diagnostic Workflow
Important V$UNDOSTAT Statistics
Step-by-Step Solutions
How to Size the Undo Tablespace
When UNDO_RETENTION Should Be Increased
Retention Guarantee: Use With Caution
Real Production Scenario
Oracle RAC Considerations
Active Data Guard Considerations
Flashback Query and Undo
Backup and Maintenance Windows
Useful Oracle Diagnostic Queries
Oracle DBA Troubleshooting Checklist
Common Mistakes
Best Practices
Frequently Asked Questions
Related Oracle Articles
About the Author
Conclusion

What Is ORA-01555: Snapshot Too Old?

ORA-01555: snapshot too old occurs when Oracle cannot obtain the older version of data required to provide a consistent read for a query.

Oracle uses Undo information to reconstruct earlier versions of database blocks. When a query begins, Oracle must provide a logically consistent view of the data according to the query's read-consistency requirements.

If the query continues running while other sessions modify data, Oracle may need older block versions from Undo. If the required Undo information has been reused before Oracle needs it, the database can no longer reconstruct the required version and the query may fail with ORA-01555.

ORA-01555: snapshot too old

The error is particularly common with:

Long-running reporting queries
Large analytical queries
Data warehouse workloads
Batch processing
Large exports
Heavy OLTP workloads occurring concurrently with reporting
Queries with inefficient execution plans

If you are troubleshooting a different recovery-related problem, such as ORA-01194: File Needs More Recovery to Be Consistent, see the related guide as well.

How Oracle Read Consistency Works

Oracle Database provides multi-version read consistency. A query should be able to see a logically consistent view of data rather than simply returning whatever happens to be committed at each individual block read.

Consider this simplified scenario:

A reporting query starts at 10:00 AM.
At 10:05 AM, another session updates a large number of rows.
The reporting query continues executing.
Oracle may need older versions of modified blocks to reconstruct the consistent view required by the query.

Those older versions are obtained from Undo information.

Key Point

ORA-01555 does not simply mean "the Undo Tablespace is small." It means Oracle could not obtain the required historical information for the consistent read. Undersized Undo, high Undo generation, long query duration, and workload concurrency can all contribute to that condition.

How Undo Is Related to ORA-01555

Oracle Undo information is used for several important purposes, including:

Transaction rollback
Read consistency
Recovery-related processing
Oracle Flashback Query and other Flashback features that depend on available Undo

When transactions modify data, Oracle generates Undo information associated with those changes. Older committed Undo can eventually become reusable according to Oracle's Undo management and retention mechanisms.

Oracle automatically tunes Undo retention based on factors such as Undo tablespace size and system activity. The UNDO_RETENTION parameter specifies a minimum retention target, but its ability to be honored depends on available Undo space. :contentReference[oaicite:1]{index=1}

Therefore, increasing UNDO_RETENTION without addressing insufficient Undo capacity or excessive Undo generation may not solve the underlying problem.

Common Causes of ORA-01555

There is rarely one universal cause. The following conditions are among the most important factors to investigate.

1. Long-Running Queries

The longer a query runs, the longer Oracle may need historical versions of blocks that are being modified by other transactions.

A query that normally completes in two minutes may be harmless, while the same query taking two hours during a heavy batch window may become vulnerable.

2. High Undo Generation

A database performing large numbers of INSERT, UPDATE, and DELETE operations can generate Undo rapidly. High Undo generation reduces the amount of elapsed time for which a fixed amount of Undo space can preserve older information.

3. Insufficient Undo Capacity

If the Undo tablespace cannot accommodate the workload and required retention window, Oracle may have to reuse Undo that would otherwise remain available for consistent reads.

4. Frequent Commits in Application Code

Frequent commits can contribute to difficult Undo/read-consistency scenarios, particularly in application patterns that repeatedly modify and query data while a cursor remains open.

Committing after every row is generally poor transaction design and should not be used simply as a method of controlling Undo usage.

5. Inefficient SQL Execution Plans

A query that performs unnecessary full table scans, excessive joins, inefficient access paths, or other expensive operations may run far longer than necessary.

Reducing execution time can reduce the period during which the query depends on older versions of data.

6. Heavy Concurrent DML

A long-running report executed while batch jobs are modifying millions of rows is a classic environment in which Undo pressure can become significant.

7. Workload Scheduling Problems

Running large reports, ETL jobs, statistics operations, and high-volume transactional processing simultaneously can create avoidable contention for system resources and increase Undo pressure.

ORA-01555 Diagnostic Workflow

ORA-01555
     |
     v
Identify affected SQL
     |
     v
How long did the query run?
     |
     v
Check V$UNDOSTAT
     |
     +----> SSOLDERRCNT
     |
     +----> NOSPACEERRCNT
     |
     +----> MAXQUERYLEN
     |
     +----> TUNED_UNDORETENTION
     |
     v
Check Undo Tablespace Capacity
     |
     v
Check Undo Generation / Heavy DML
     |
     v
Review SQL Execution Plan
     |
     v
Review Application Commit Pattern
     |
     v
Optimize Workload / SQL
     |
     v
Resize Undo if Required
     |
     v
Re-test and Monitor

This approach is preferable to changing parameters immediately. The objective is to identify the evidence behind the failure before applying a configuration change.

Important V$UNDOSTAT Statistics

V$UNDOSTAT is one of the most useful views for diagnosing Undo-related problems. It contains statistics collected at regular intervals and provides information about Undo consumption, transactions, long-running queries, and retention. Oracle documentation specifically identifies this view as useful for monitoring and estimating Undo requirements. :contentReference[oaicite:2]{index=2}

SELECT
    BEGIN_TIME,
    END_TIME,
    UNDOBLKS,
    TXNCOUNT,
    MAXQUERYLEN,
    SSOLDERRCNT,
    NOSPACEERRCNT,
    TUNED_UNDORETENTION
FROM V$UNDOSTAT
ORDER BY BEGIN_TIME;

Column	Why It Matters
`BEGIN_TIME`	Beginning of the statistics interval.
`END_TIME`	End of the statistics interval.
`UNDOBLKS`	Undo blocks consumed during the interval.
`TXNCOUNT`	Transaction activity during the interval.
`MAXQUERYLEN`	Maximum query duration observed during the interval.
`SSOLDERRCNT`	Number of snapshot-too-old errors observed during the interval.
`NOSPACEERRCNT`	Number of times Undo space was requested when no free Undo space was available.
`TUNED_UNDORETENTION`	Retention period Oracle was able to tune for the current workload, expressed in seconds.

A particularly important combination is:

SSOLDERRCNT > 0
High MAXQUERYLEN
High Undo consumption
Low TUNED_UNDORETENTION compared with query duration
Evidence of Undo space pressure

Oracle documents SSOLDERRCNT as the count of ORA-01555 errors and NOSPACEERRCNT as the count of situations where Undo space was requested but no free space was available. :contentReference[oaicite:3]{index=3}

Step-by-Step Solutions

The correct solution depends on the evidence collected during diagnosis. The following approaches should be considered in combination rather than as a single universal fix.

Solution 1 – Optimize the SQL Statement

If the query takes unnecessarily long to execute, SQL tuning should be one of the first corrective actions.

Review the execution plan and look for:

Unnecessary full table scans
Missing or inappropriate indexes
Excessive joins
Incorrect join methods
Stale or inappropriate optimizer statistics
Unnecessary sorting or aggregation
Excessive logical or physical I/O
Unnecessary data being returned to the application

EXPLAIN PLAN FOR
SELECT ...
FROM ...;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

Where possible, also examine the actual cursor execution statistics rather than relying exclusively on an estimated execution plan.

Solution 2 – Avoid Unnecessary Commits Inside Loops

A common application anti-pattern is committing after every individual row.

Problematic pattern:

FOR i IN 1..10000 LOOP

    UPDATE employees
    SET salary = salary + 100
    WHERE employee_id = i;

    COMMIT;

END LOOP;

The transaction design should be based on business consistency requirements rather than using frequent commits simply to "save Undo."

Better pattern for an appropriate transaction:

FOR i IN 1..10000 LOOP

    UPDATE employees
    SET salary = salary + 100
    WHERE employee_id = i;

END LOOP;

COMMIT;

For very large batch operations, carefully designed batch commits may be appropriate, but they should be based on transaction boundaries and application requirements—not used as an automatic cure for ORA-01555.

Production Tip

Whenever possible, prefer efficient set-based SQL instead of row-by-row processing. A single properly designed SQL statement can be substantially more efficient than thousands of procedural updates.

Solution 3 – Increase Undo Tablespace Capacity

If workload analysis shows that the Undo tablespace is undersized, increase its capacity.

Resize an existing datafile:

ALTER DATABASE DATAFILE '/path/undo01.dbf'
RESIZE 2G;

Add another Undo datafile:

ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/path/undo02.dbf'
SIZE 1G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 10G;

The actual size and maximum size must be selected according to workload, Undo generation rate, retention requirements, and available storage capacity.

Important

Do not use MAXSIZE UNLIMITED as a blanket production recommendation. Autoextend should be combined with capacity monitoring and an appropriate storage limit where operational policy requires one.

Solution 4 – Review UNDO_RETENTION

Check the current setting:

SHOW PARAMETER undo_retention;

Or:

SELECT
    NAME,
    VALUE
FROM V$PARAMETER
WHERE NAME = 'undo_retention';

If a longer retention window is genuinely required, you can change the parameter:

ALTER SYSTEM
SET UNDO_RETENTION = 1800
SCOPE = BOTH;

However, increasing the parameter does not automatically create more Undo space. Oracle documentation describes UNDO_RETENTION as a minimum retention target and notes that it can only be honored when sufficient Undo space is available. :contentReference[oaicite:4]{index=4}

Solution 5 – Monitor TUNED_UNDORETENTION

The configured UNDO_RETENTION value is not the same thing as the retention Oracle is actually able to provide under the workload.

Check the tuned retention:

SELECT
    BEGIN_TIME,
    END_TIME,
    MAXQUERYLEN,
    TUNED_UNDORETENTION
FROM V$UNDOSTAT
ORDER BY END_TIME;

Compare the query duration with TUNED_UNDORETENTION. If long-running queries regularly exceed the retention Oracle can maintain under peak workload, investigate Undo capacity and workload generation rather than simply increasing the parameter.

Oracle documents TUNED_UNDORETENTION as the retention period determined by the database based on the current workload and Undo configuration. :contentReference[oaicite:5]{index=5}

Solution 6 – Reduce Heavy Concurrent DML

If a reporting query runs while another process modifies millions of rows, the Undo generation rate may become substantially higher than normal.

Consider:

Scheduling reports outside peak batch windows.
Moving large reporting workloads to an appropriate reporting platform.
Reducing unnecessary DML.
Breaking large maintenance operations into carefully planned batches.
Optimizing ETL operations.
Reducing unnecessary repeated updates.

Solution 7 – Use RETENTION GUARANTEE Carefully

Oracle supports Undo retention guarantee for environments where preserving unexpired Undo is more important than allowing transactions to continue consuming space.

ALTER TABLESPACE UNDOTBS1
RETENTION GUARANTEE;

Check the current configuration:

SELECT
    TABLESPACE_NAME,
    RETENTION
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';

Warning

With retention guarantee enabled, Oracle will not overwrite unexpired Undo merely to satisfy new Undo requirements. If the tablespace runs out of usable space, DML operations can fail. Oracle therefore recommends using this option carefully. :contentReference[oaicite:6]{index=6}

How to Size the Undo Tablespace

Increasing Undo from 4 GB to 8 GB, or from 8 GB to 16 GB, should not be treated as a universal formula. A better approach is to evaluate actual Undo generation and the retention window required by the workload.

A simplified planning concept is:

Required Undo Capacity ≈ Peak Undo Generation Rate × Required Retention Time + Safety Margin

For example, if a workload generates approximately 200 MB of Undo per minute during a peak window and you need to preserve approximately 60 minutes of Undo, the base requirement would be approximately:

200 MB/minute × 60 minutes
= 12,000 MB
≈ 11.7 GB

This is only an illustrative calculation. Real production sizing should account for workload variability, concurrent transactions, peak Undo generation, retention requirements, storage constraints, and operational safety margins.

Oracle also provides Undo statistics and advisory mechanisms that can be used to estimate appropriate Undo capacity for the workload. :contentReference[oaicite:7]{index=7}

When Should UNDO_RETENTION Be Increased?

Do not increase UNDO_RETENTION automatically every time ORA-01555 appears.

Investigate first:

How long the affected query runs.
How much Undo the workload generates.
What TUNED_UNDORETENTION shows.
Whether SSOLDERRCNT is increasing.
Whether the Undo tablespace is experiencing capacity pressure.
Whether SQL tuning can significantly reduce query duration.
Whether the workload can be rescheduled.

Oracle generally recommends leaving the parameter at its default unless there is a specific requirement, such as Flashback-related retention or Active Data Guard requirements. :contentReference[oaicite:8]{index=8}

Representative Production Scenario

Consider a month-end financial reporting environment running Oracle Database 19c.

Environment:

Oracle Database 19c
Heavy OLTP workload
Undo Tablespace: 4 GB
Report execution time: approximately 70 minutes
Continuous UPDATE and INSERT activity

The reporting query fails with:

ORA-01555: snapshot too old

Investigation identifies:

The report runs for an unnecessarily long period.
Undo generation increases significantly during month-end processing.
The query contains inefficient access paths.
Undo retention available during the peak workload is insufficient for the query's duration.

Corrective actions:

Optimize the reporting SQL.
Improve appropriate indexing and access paths.
Increase Undo capacity based on measured workload requirements.
Configure autoextend with appropriate capacity limits.
Review the retention requirement.
Schedule heavy reporting outside the most intensive DML window where possible.

Suppose SQL tuning reduces the report from approximately 70 minutes to 8 minutes. That reduction alone can substantially reduce the period during which the query depends on older versions of modified data.

Lesson:

The strongest solution is often a combination of SQL optimization, appropriate Undo sizing, and workload management—not a single initialization parameter.

Oracle RAC Considerations

Oracle RAC environments can also experience ORA-01555. Each RAC instance normally uses an Undo tablespace associated with that instance, so Undo pressure should be evaluated on an instance-by-instance basis.

When troubleshooting RAC, review:

Undo tablespace capacity for each instance.
Undo generation during peak workload.
Long-running SQL on each instance.
Service and workload distribution.
Execution plans across instances.
Batch jobs and reporting workloads.

One heavily loaded RAC instance can experience significant Undo pressure even when another instance appears relatively quiet.

RAC Best Practice

Monitor Undo statistics per instance and correlate ORA-01555 occurrences with the workload running on the affected instance.

Active Data Guard Considerations

Long-running read-only workloads against an Active Data Guard standby can also have Undo-retention requirements.

When troubleshooting these environments, consider:

Standby reporting workload.
Redo apply activity.
Query duration.
Undo retention requirements on the primary.
Workload concurrency.

Oracle specifically documents scenarios in which increased Undo retention on the primary may be required to support query requirements on Active Data Guard standby instances. :contentReference[oaicite:9]{index=9}

Flashback Query and Undo

Several Oracle Flashback features depend on historical information being available for the required period.

For example, a Flashback Query can request data as it existed at an earlier point in time. If the required Undo information is no longer available, the historical query may fail.

This is why Undo sizing and retention are important not only for long-running queries but also for workloads that depend on historical data access.

Important distinction: Flashback Query and other Undo-dependent Flashback operations should not be confused with Flashback Database, which uses flashback logs as part of its database-level recovery mechanism.

ORA-01555 During Backup and Maintenance Windows

ORA-01555 is not simply an "RMAN error." RMAN backup activity and other maintenance operations can, however, coincide with workloads that generate substantial database activity.

During busy maintenance windows, monitor:

Undo consumption.
Long-running reports.
Large batch updates.
Statistics gathering.
ETL workloads.
Concurrent application activity.

The objective is to determine whether the backup or maintenance window overlaps with the workload that is actually generating the Undo pressure.

Useful Oracle Diagnostic Queries

1. Check Current Undo Tablespace

SHOW PARAMETER undo_tablespace;

2. Check UNDO_RETENTION

SHOW PARAMETER undo_retention;

3. Check Undo Tablespace Files

SELECT
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES / 1024 / 1024 AS SIZE_MB,
    AUTOEXTENSIBLE,
    MAXBYTES / 1024 / 1024 AS MAX_SIZE_MB
FROM DBA_DATA_FILES
WHERE CONTENTS = 'UNDO'
ORDER BY TABLESPACE_NAME, FILE_NAME;

4. Check Undo Tablespace Retention Mode

SELECT
    TABLESPACE_NAME,
    STATUS,
    RETENTION
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';

5. Check ORA-01555 Statistics

SELECT
    BEGIN_TIME,
    END_TIME,
    SSOLDERRCNT,
    NOSPACEERRCNT,
    MAXQUERYLEN,
    TUNED_UNDORETENTION
FROM V$UNDOSTAT
ORDER BY END_TIME;

6. Find Intervals With Snapshot Too Old Errors

SELECT
    BEGIN_TIME,
    END_TIME,
    SSOLDERRCNT,
    MAXQUERYLEN,
    TUNED_UNDORETENTION
FROM V$UNDOSTAT
WHERE SSOLDERRCNT > 0
ORDER BY END_TIME;

7. Find Intervals With Undo Space Pressure

SELECT
    BEGIN_TIME,
    END_TIME,
    NOSPACEERRCNT,
    UNDOBLKS,
    TXNCOUNT
FROM V$UNDOSTAT
WHERE NOSPACEERRCNT > 0
ORDER BY END_TIME;

8. Review Maximum Query Duration

SELECT
    BEGIN_TIME,
    END_TIME,
    MAXQUERYLEN,
    TUNED_UNDORETENTION
FROM V$UNDOSTAT
ORDER BY END_TIME;

Note: MAXQUERYLEN reports the maximum query duration observed during the statistics interval. It does not identify the SQL statement itself.

9. Review Current Long-Running Sessions

SELECT
    SID,
    SERIAL#,
    USERNAME,
    STATUS,
    SQL_ID,
    LAST_CALL_ET
FROM V$SESSION
WHERE STATUS = 'ACTIVE'
  AND USERNAME IS NOT NULL
ORDER BY LAST_CALL_ET DESC;

Use the resulting SQL_ID values with appropriate SQL performance views to investigate the actual SQL statements and execution statistics.

Oracle DBA Troubleshooting Checklist

Use this checklist before changing Undo parameters in production.

Check	Completed
Identify affected SQL	☐
Measure query duration	☐
Check SSOLDERRCNT	☐
Check NOSPACEERRCNT	☐
Check MAXQUERYLEN	☐
Check TUNED_UNDORETENTION	☐
Check UNDO_RETENTION	☐
Check Undo tablespace size	☐
Check AUTOEXTEND configuration	☐
Check Undo generation rate	☐
Review execution plan	☐
Check heavy concurrent DML	☐
Review application commit frequency	☐
Review workload scheduling	☐
Resize Undo if evidence supports it	☐
Re-test after corrective action	☐

Common Mistakes When Troubleshooting ORA-01555

Increasing UNDO_RETENTION without checking Undo capacity.
Assuming every ORA-01555 is caused by a small Undo tablespace.
Ignoring SQL execution time.
Ignoring heavy concurrent DML.
Using frequent commits as a generic Undo-management strategy.
Ignoring inefficient execution plans.
Setting MAXSIZE UNLIMITED without storage-capacity planning.
Enabling RETENTION GUARANTEE without understanding its DML failure risk.
Using MAXQUERYLEN as though it identifies a specific SQL statement.
Failing to monitor SSOLDERRCNT and NOSPACEERRCNT.
Changing multiple parameters simultaneously without establishing a baseline.

Myths About ORA-01555

Myth	Reality
Increase UNDO_RETENTION and the problem will disappear.	Not necessarily. Undo capacity and workload generation also matter.
Only small Undo tablespaces cause ORA-01555.	Long-running SQL and high concurrent Undo generation can also contribute.
More RAM fixes Snapshot Too Old.	Memory is not a direct substitute for appropriate Undo capacity and retention.
Frequent commits always reduce ORA-01555 risk.	Frequent commits can introduce application-level consistency problems and should not be treated as a universal fix.
RETENTION GUARANTEE is always the best solution.	It protects unexpired Undo but can cause DML operations to fail when space is exhausted.

Oracle Best Practices for Preventing ORA-01555

Monitor V$UNDOSTAT regularly.
Track SSOLDERRCNT and NOSPACEERRCNT.
Review TUNED_UNDORETENTION during peak workload periods.
Size Undo based on measured workload requirements.
Optimize long-running SQL statements.
Avoid unnecessary full table scans and inefficient access paths.
Prefer set-based SQL over row-by-row processing where appropriate.
Avoid unnecessary commits inside loops.
Use autoextend with appropriate storage limits and monitoring.
Schedule large reports outside heavy DML windows where possible.
Monitor batch and ETL workloads for Undo spikes.
Review execution plans for frequently failing reports.
Use Undo retention guarantee only when the business requirement justifies its operational risk.
Establish a baseline before changing production configuration.

Frequently Asked Questions

What is the primary cause of ORA-01555?

The immediate problem is that Oracle cannot obtain the historical information required for a consistent read. The underlying reason may be insufficient effective Undo retention, high Undo generation, long query duration, or workload-related pressure.

Does increasing UNDO_RETENTION always solve ORA-01555?

No. UNDO_RETENTION is not an independent guarantee of available Undo. The Undo tablespace must have sufficient capacity for the workload and required retention period.

What does SSOLDERRCNT mean?

SSOLDERRCNT records the number of snapshot-too-old errors observed during the corresponding V$UNDOSTAT interval. It is one of the most useful statistics for confirming that ORA-01555 occurred during a particular workload period.

What is TUNED_UNDORETENTION?

TUNED_UNDORETENTION represents the Undo retention period Oracle is able to tune for the workload and current Undo configuration. It is expressed in seconds and can be compared with query duration when investigating ORA-01555.

Can SQL tuning prevent ORA-01555?

Yes. Reducing unnecessary query execution time can significantly reduce the period during which the query depends on older versions of modified data.

Can frequent COMMIT cause ORA-01555?

Frequent commits can contribute to certain snapshot-too-old scenarios, especially in application patterns involving long-running cursors and repeated modifications. However, the exact cause should be established from the workload rather than assuming that commit frequency alone caused the error.

Does AUTOEXTEND prevent ORA-01555?

No. Autoextend can provide additional Undo capacity when storage is available, but it does not replace SQL tuning, workload management, or proper retention planning.

Does Oracle RAC prevent ORA-01555?

No. RAC environments can also experience ORA-01555. Undo usage and workload should be evaluated for each RAC instance.

Can Flashback Query be affected by Undo availability?

Yes. Flashback Query and other Undo-dependent Flashback operations require historical information to remain available for the requested period.

Should I enable RETENTION GUARANTEE?

Only when there is a clear requirement for guaranteed Undo retention and the environment can tolerate DML failures if the Undo tablespace becomes unable to provide additional space. Oracle explicitly warns that retention guarantee can cause DML operations to fail. :contentReference[oaicite:10]{index=10}

How do I know whether Undo is undersized?

Review Undo generation, NOSPACEERRCNT, query duration, TUNED_UNDORETENTION, tablespace capacity, and peak workload periods. Use these measurements together rather than relying on a single parameter.

About the Author

Rana Abdul Wahid is a seasoned Oracle Database Consultant with more than 15 years of professional experience in Oracle Database Administration, Oracle E-Business Suite Application DBA, Oracle Cloud Infrastructure (OCI), Oracle RAC, Oracle Data Guard, RMAN Backup & Recovery, Performance Tuning, Linux Administration, MySQL, Microsoft SQL Server, PostgreSQL, and enterprise database infrastructure.

Through this Oracle DBA knowledge base, he shares practical troubleshooting techniques, production-oriented solutions, Oracle administration best practices, and database administration knowledge developed through enterprise database environments.

Learn more about the author →

Conclusion

The ORA-01555: Snapshot Too Old error is fundamentally a read-consistency problem: Oracle needs historical information to reconstruct the data required by a query, but the required Undo information is no longer available.

The most effective troubleshooting approach is therefore not to change UNDO_RETENTION blindly. Instead, investigate the complete workload: query duration, Undo generation, Undo capacity, SSOLDERRCNT, NOSPACEERRCNT, TUNED_UNDORETENTION, execution plans, concurrent DML, and application transaction design.

Increasing Undo capacity may be necessary. Increasing UNDO_RETENTION may be appropriate in specific environments. SQL optimization may provide an even greater improvement. Workload scheduling may eliminate the problem altogether.

Final DBA Advice

Do not treat ORA-01555 by increasing parameters alone. Measure the workload, identify why the required Undo became unavailable, optimize the SQL where possible, size Undo based on evidence, and monitor the result after each corrective action. A systematic diagnosis is more reliable than changing configuration parameters in isolation.

Originally Published: April 2026
Last Updated: August 2026

Oracle Database Error Solutions & DBA Knowledge Base

ORA-01555 Snapshot Too Old Error in Oracle Database: Causes, Diagnosis, Solutions & Prevention