Snapshot too old

[Patrick Van der Sande]

Dear,   Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches) When the job is running, certain batches stop with ORA-1555 : Snapshot too old. Other batches run well till the end.   Bizarre is that not always the same job stops.   When I do a trace I see nothing. With a normal trace I am pretty sure that I will never see it. Rollback segments are rarely used. So making the rollbacks bigger or smaller is not the solution. They also tried to change the commit rate. That was not the solution.   When I modified the optimal size to NULL value to avoid shrinking and cached 3 heavily used sequences some runs went all the way but since a week it stops again with the same annoying error.   After that I put an event in the init.ora file : event = "1555 trace name processstate forever, level 10" A trace file was generated but I could not find the error in the trace file. I am pretty sure that Oracle just dumps all open cursors in a file. Since there are 100 of cursors opened I do not have a clue which one is provoking the error.   I already looked at the batches and I have identified in 5 of them a “fetch across commit”. Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !)   So my question is :   How can I know where in the code the error is generated ? Must I change the definition of the event ? (I know there are other options but I can not find them right away) Should I use DBMS_PROFILER ? (it generates massive files !) Must they write exceptions everywhere in their code ?   Can somebody help me ?   Please do not send me an explanation of the “snapshot too old” error. I wake up with it and I go asleep with it.     Patrick