IBM Support

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

Troubleshooting


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.

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, 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.

[{"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"DB Connections\/Connection Pooling","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF012","label":"IBM i"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"9.0;8.5.5;8.0;7.0","Edition":"Edition Independent","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SSNVBF","label":"Runtimes for Java Technology"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Java SDK","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21188547