ORA-01555 means Oracle could not reconstruct the consistent-read image a query needed because the undo records for older changes had been overwritten. You can reproduce the failure safely by running a long query while concurrent transactions repeatedly update and commit the rows it reads, but the fix is not simply to raise UNDO_RETENTION: available undo space, workload, and retention requirements determine what Oracle can preserve.
What ORA-01555 means
Oracle’s ORA-01555 error reference describes the cause as “rollback records needed by a reader for consistent read are overwritten by other writers.” Oracle queries use undo to present a consistent view of data, even as other transactions change it. If a query needs an older version of a row but the required undo has been reused, Oracle cannot reconstruct that view and the query fails.
This commonly becomes a risk when a query runs for a long time while concurrent transactions make and commit changes. The reader may need undo created before those changes; continued undo generation can put pressure on the available space and make older records eligible for reuse.
How to reproduce ORA-01555 safely
Use a non-production database with a representative schema and undo configuration. Oracle documents the underlying failure mechanism; the steps below are an operational way to exercise it, not a guarantee that a particular runtime or workload will trigger the error on every release.
#1 Best Overall
- Choose a query that reads a substantial set of rows and is likely to remain active long enough to overlap with concurrent changes.
- Start the query and keep it running so that it must preserve a consistent read of the data it accesses.
- From other sessions, repeatedly update rows the query reads and commit the transactions. Use a workload that reflects the undo pressure you are trying to investigate.
- Observe whether the query completes or reports ORA-01555. Record the query runtime, workload timing, and undo configuration; do not treat a successful run as proof that production workloads are safe.
Do not try to force the error in production. A historical Oracle8 error reference describes a separate, legacy-specific risk in precompiler applications that fetch across commits without closing a cursor. It is not the general explanation for modern Automatic Undo Management workloads, and it is not a suitable production test.
How to diagnose the cause before changing settings
Establish the undo configuration
Record the database release and undo management mode. Check the undo tablespace size, whether it can autoextend, its maximum size, and whether RETENTION GUARANTEE is enabled. These details determine how much undo can be kept and what Oracle may do when space is under pressure.
Compare the query duration with undo activity
Identify the failing SQL and its runtime. In V$UNDOSTAT, Oracle documents MAXQUERYLEN as a longest-query-duration measure and UNDOBLKS as the number of undo blocks consumed during each ten-minute interval. Review successive intervals and correlate undo-generation spikes with periods of concurrent updates. These measures help reveal workload patterns; a single value does not establish an exact tablespace size requirement.
Check whether the operation needs more than ordinary query retention
Determine whether the failure concerns a long-running query or a Flashback operation. Flashback can require undo older than the longest active query, so query-duration monitoring alone may not describe its retention needs. Investigate LOB workloads separately: Oracle documentation notes that automatic undo-retention tuning does not apply to LOB undo, and unexpired LOB undo may be overwritten when space is low.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →How to fix ORA-01555
Oracle’s current error reference recommends increasing UNDO_RETENTION when using Automatic Undo Management, or using larger rollback segments otherwise. Treat that as a starting point, not a standalone guarantee: a retention setting requests a time horizon, but does not create capacity for the undo your workload generates.
Size capacity for workload and retention demand
In Automatic Undo Management, assess peak undo generation alongside the retention horizon required by queries and Flashback operations. Then set a meaningful retention target and ensure the undo tablespace can support it under the relevant workload. Retention that is achievable in a lightly loaded period may not be achievable when undo generation rises.
Check growth limits on autoextending tablespaces
If the undo tablespace uses autoextend, verify both available storage headroom and the tablespace’s MAXSIZE. Reaching the growth ceiling can leave Oracle unable to preserve the requested retention; the system may overwrite unexpired undo rather than grow beyond that limit.
Make fixed-size undo large enough
A fixed-size undo tablespace that is too small can cause both long-query ORA-01555 failures and errors for new DML when there is not enough room for new transactions. The effective retention it can support changes with system load, so size it against the workload rather than assuming a retention target will be met regardless of activity.
Choose deliberately whether to use RETENTION GUARANTEE
RETENTION GUARANTEE protects unexpired undo from being overwritten, but shifts the risk: if space is needed for new transactions and cannot be reclaimed without violating the guarantee, DML may fail. Use it only when preserving unexpired undo matters more than uninterrupted DML.
Rank #4
Address manual rollback-segment management by release
If the database uses manual rollback-segment management, follow the documented guidance for that release. Oracle’s current error action says to use larger rollback segments; do not apply obsolete pre-AUM settings without confirming that they apply to your configuration.
Reduce avoidable pressure where the application permits
Shorten unnecessarily long queries and smooth unusually high concurrent undo generation when application requirements allow. These steps follow from the failure mechanism, but their value depends on the workload; they are not a quantified guarantee against ORA-01555.
Why ORA-01555 can persist after increasing UNDO_RETENTION
UNDO_RETENTION cannot guarantee that Oracle will retain undo if the tablespace lacks capacity for the workload. In an autoextending tablespace, a storage shortage or maximum-size limit can prevent further growth; in a fixed-size tablespace, high undo generation can shorten the retention the space can actually support. With insufficient capacity, Oracle may reuse undo needed by a reader. A retention guarantee changes that pressure trade-off by protecting unexpired undo at the possible expense of new DML.
Best Value
Flashback requirements and LOB undo also need separate consideration: Flashback may require older undo than ordinary queries, while automatic undo-retention tuning does not apply to LOB undo.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose an undo strategy against the actual requirement
Before changing configuration, compare the requirement and pressure points that matter for your database:
- Retention horizon: establish whether the need is an ordinary query’s consistent read or a Flashback operation that may reach further back.
- Capacity and growth ceiling: account for current undo space, autoextend headroom, and maximum size—or size a fixed tablespace for the workload.
- Behavior under pressure: decide whether undo reuse and possible query failure are preferable to a guarantee that may cause DML failure.
- Undo generation: use interval data to identify peak consumption and concurrent workload windows rather than relying on a single statistic.
- LOB and release details: validate LOB behavior and use documentation appropriate to the database release and undo mode.
For configuration details, see Oracle’s 26ai Managing Undo documentation, which covers automatic tuning, space constraints, Flashback, and V$UNDOSTAT. Oracle’s 18c Managing Undo documentation discusses fixed-size undo risks and LOB retention qualifications. The Oracle Manage Undo Data tutorial explains Undo Advisor and retention guarantee considerations.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minute
