java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 2 with name "RB1" too small
 Technote (troubleshooting)
 
Problem(Abstract)
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.

 
Resolving the problem
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.
 
 
Cross Reference information
Segment Product Component Platform Version Edition
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > DB Connections/Connection Pooling
Operating system(s): Windows
Software version: 6.0
Software edition:
Reference #: 1188547
IBM Group: Software Group
Modified date: Aug 16, 2004