Re: Fetch across commit

Lilly wrote in message <390ED62B.C42D50CA_at_Lilly.COM>...
>i read about a feature called 'fetch across commit', which
>will avoid rollback segment overflows when using a cursor.
>Can somebody send me an example of usage?

"Fetch across commit" is a programming technique where you open a cursor, fetch rows from it, update the rows and commit the updates within the cursor loop. ie. your cursor loop is open (and fetching) across one or more commits. eg:

     open c1;

      loop

         fetch c1 into c1_rec;

         exit when c1%NOTFOUND;

         if ...... then

            update <table opened by cursor>
               set ......
             where rowid = c1_rec.rowid;

            commit_count := commit_count + 1;

            if commit_count > 5000 then
               commit;
               commit_count := 0;
            end if;

         end if;

      end loop;

      close c1;

      commit;

As you're committing every so often, your rollback segments don't need to be big enough (or grow enough) to hold all the uncommitted updates for the entire result set of the C1 cursor at the same time.

However, this technique brings a load of its own problems in the form of the ORA-1555 "Snapshot too old" error condition. So "fetch across commit" is not normally recommended as a desirable technique.

There have been many threads in this newsgroup about "Snapshot too old", mostly caused by fetch across commit. Use Dejanews or some other newsgroup archive site to see them. I've also got some notes at http://home.clara.net/dwotton/dba/snapshot.htm which explains the problem in more detail.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.