Oracle error: Ora-01555 : snapshot too old: rollback segment number 1......
System Error: Unknown Database error (type qqdb_ResourceException) on WMS_WH1: Execute failed for SQL statement ............... error from database is: ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small...
Any ideas ? Please help.
You either have
- an UNDO tablespace that is way too small (rollback segments that are too small); or
- an application that does way too many COMMITs (common with apps ported from SQL Server)
From the added info, I would guess that the Java program has a major loop and does processing on each of the values it gets in that loop. At the end of each turn in the loop, it does a commit. Then it tries to look at the next record in the master list. After a while (say around 388 loops ;-) ) it dies.
The reason is that when it looks for information in the master query, it needs to rebuild that information to the moment that query was initiated. It uses rollback (AKA undo) to rebuild that. But rollback is not guaranteed to exist after a commit (unless forced) and once a commit happens, that area can be reused. When it is reused, the information is no longer there to rebuild the row, and that raises an ORA-01555
Edited by: Hans Forbrich on Sep 11, 2009 3:44 PM