ORA-01555: snapshot too old: rollback segment number with name "" too small

The following video provides you with the correct English pronunciation of the word "ORA-01555", to help you become a better English speaker.

snapshot too old (rollback segment too small)

---

Section: 01500-01699 Oracle Commands Messages
Notes: This section lists messages generated when entering Oracle commands.If you are using Trusted Oracle, see the Trusted Oracle documentation for information about error messages in that environment.

---

Cause: One of the following:

- Insufficient Rollback Segments: A long running query may not be able to reconstruct the snapshot of the blocks it is reading because the rollback data is not available. This can happen when the database has many transactions that are changing data, then committing or rolling back. The rollback data can be overwritten if the rollback segments are too small for the number and size of the changes being made.
- Precompiler - Insufficient Rollback Segments: A long running query may not be able to reconstruct the snapshot of the blocks it is reading because the rollback data is not available. This can happen if your program does not CLOSE a cursor after repeated FETCH or UPDATE statements. Alternatively, if a FETCH is executed after a COMMIT, the number of rollback records created since the last CLOSE of the current cursor will fill the available rollback segments and begin to overwrite earlier records.

Action: For the above causes:

- Insufficient Rollback Segments; Make a larger number of bigger rollback segments available. This will allow the rollback data for completed transactions to be kept longer.
- Precompiler - Insufficient Rollback Segments: If you are not using the fetch across commit feature, ensure that you have CLOSEd cursors appropriately. Otherwise, you must increase the number and the size of rollback segments. You can estimate the size of the rollback data that your program will produce with the V$ROLLSTAT view, which contains the number of bytes written for each rollback segment. Set the current transaction to a rollback segment in a single user mode and query V$ROLLSTAT before and after the transaction. The difference in V$ROLLSTAT gives the number of rollback data bytes written for the transaction. Estimate the total number of transactions done in the loop and, after considering other concurrent transactions, create rollback segments accordingly.

Note: Fetch across commit is not supported by the ANSI standard. According to the ANSI standard, a cursor is invalidated when a commit is performed and should be closed and reopened. With Oracle you can fetch across commit. However, you should be aware that you may get this error.

---

Provided by PB for Oracle Professional.

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause: rollback records needed by a reader for consistent read are
overwritten by other writers
*Action: Use larger rollback segments