>>Interesting. Are you telling the Oracle version of the code had no
>>intermittent COMMIT and relied on one final COMMIT at the end. Even
>>in Oracle developers must have planned for commit since a long running
>>open transaction can lead to “snapshot too old” error.
>Yes, I am saying just that. With one important clarification: there were
>no transactions as SELECT does not open them and the application does not
>change anything on that connection. So, no 'snapshot too old' and no COMMITs.
It's been a while since I worked with Oracle as a developer. But my understanding
is that even a read-only transaction, like the one you described above, requires
a point in time consistent image of the database. This would imply that if your
transaction runs for a long time and meanwhile other DML sessions change lot of blocks,
resulting in undo tablespace getting totally turned over, then Oracle can no longer
gurantee PIT consistent view of the database to your session and barf out with
snapshot-too-old error.
I have no way of confirming this and I am writing this based on my limited experience
with oracle. So I may be wrong.
