Infamous snapshot too old error

I have a code that works like this:

...
cursor c1 is
select ... <blah> ...
from ... <blah> ...;

cursor c2 is
select a.*
from table1 a
where a.flag = 1;

begin
  for r1 in c1
  loop
    insert into table1
(flag, ...)

    values
(1, ...);

    commit;
  end loop;
  commit;

  for r2 in c2
  loop
    insert into table1
(flag, ...)

    values
(2, ...);

    commit;
  end loop;
  commit;
end ;
...

As the amount of data inserted by c1 and c2 are many, I am getting "ORA-1555: snapshot too old" error in c2 loop frequently. I am aware of the common remedy being:

(1) avoid use of fetch across commit in c2
(2) increase size of rollback segment
(3) break up cursor c2 recordset so that fewer

    records are updated at a time

What I do not understand, is why I would encounter such error in the first place. As c1 has commited all changes to table1, and there is no further update to data image used by c2. Therefore, there is no need for c2 to read data image from rollback segment at all. Any idea?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 30 2000 - 21:30:43 CST