Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL and Snapshot too old
Manoj Appully <manoj.appully_at_bridge.bellsouth.com> wrote:
>2. Fetches across commits with delayed block clean out.
>
>Oracle will do a fast commit when a data block is changed by making txns as committed in
>the rollback segment header but still may not have as yet cleaned the datablocks that
>were modified.
>
>Let us in this case take two tables and assume the same situation as in step 1, ie.,
>a cursor is opened on table T1, in a loop again you are fetching from T1 and changing
>records in table T2 and committing. Though you are committing on T2 you could still
>get ORA-1555 because cleanout has not been done on T1 from which records are being
>fetched.
>
>In this case a full table scan before opening and fetching thru cursor will help.
>
I too am having problems with Snapshot too old. I am reading from one table (via a cursor) and writing to another, and committing every 1000 records. The table i am reading from is NOT being updated, yet i get snapshot too old.
Your solution (No 2) cures this if i do a full table scan before my process starts; however, i am not sure I fully understand what 'Delayed Block Clean out' is.
Could you explain in more detail.
Thanks, Mike