HELP WITH SNAPSHOT TOO OLD ERROR

Question

My table is updated with 36 parallel running oracle jobs all running at the same time. All these jobs update same table but each job deal with different sequence numbers and hence they don't have any lock scenario in between them. But when they run all together some of the job fails with SNAPSHOT TOO OLD ERROR. I did check indexes, common data, undo tablespace, temp space but all look good. Can someone please help to understand why am I getting this error and how can I avoid it.

Nathan CCC · Answer

Hi, You can get this database error ORA-01555 whether a query is generated from OTBI or not. You typically get this error because a query must be "read consistent". So it must answer the question using a snapshot of the state of the data as at a single point in time. If transactions are commit while the query is running then the database must use rollback to undo those transactions to get back to the state of the data as it was at the start of the query before any insert update delete. If your query takes too long with you having too many transactions while your query is running then you get this error because it does not have enough rollback to get back to that point in time.  There are other scenarios when you can get this error too outside of long running queries.

For more information see this my oracle support article

Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small (Doc ID 1580790.1)

John Hung-Oracle · Answer

Hi, your question doesnt seem like it OTBI related.  Can you please confirm you have posted your question in the right forum. Thank you