java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 2 with name "RB1" too small

Technote (FAQ)
Problem
Steps you can take to prevent an ORA-01555 error.
Cause
Oracle uses rollback segments to store the data that is changed in a transaction. This data is used to restore the original data if a rollback occurs in the transaction. Also, the data in the rollback segment is used to guarantee read-consistency. That is, that a query always sees the same data even though it might be modified in the middle of the query.

The ORA-01555 occurs if a rollback segment runs out of space.

Solution
There are three possible solutions to prevent this error:
  1. Increase the size of the rollback segment.
    Depending on the application, this can take a significant amount of disk space on the Oracle server.The size of the rollback segment can be estimated by monitoring the V$ROLLSTAT view, which has two columns that can point the way to an answer. The RSSIZE column represents the current total space used by a rollback segment, while the HWMSIZE column represents the high-water mark, the highest point reached by RSSIZE since the last time the database instance was started.

  2. Modify the application to reduce the time to run long-running queries.

  3. For Oracle 9i and later, set the value for the UNDO_MANAGEMENT parameter to AUTO.
    When the parameter UNDO_MANAGEMENT is set to MANUAL (the default setting), the rollback segments are used, much as with the previous versions of Oracle. When the parameter UNDO_MANAGEMENT is set to AUTO the role of rollback segments are handled by undo tablespaces, which are locally-managed tablespaces containing rollback segments completely managed by the RDBMS.











Document Information

Product categories: Software, Application Servers, Distributed Application & Web Servers, WebSphere Application Server, DB Connections/Connection Pooling
Operating system(s): AIX, HPUX, Linux, Multi-Platform, Solaris, Windows
Software version: 3.5, 4.0, 5.0, 5.1, 6.0
Software edition: Edition Independent
Reference #: 1188547
IBM Group: Software Group
Modified date: 2004-10-29