Oracle Transactional Business Intelligence

Products Banner

HELP WITH SNAPSHOT TOO OLD ERROR

Received Response
21
Views
2
Comments

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.

Answers

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

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭
    edited June 12

    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)