Stop doing that. Its the number one cause of this error.

Search for 1555 on my site, you'll see many examples and the reasons for this error.

The #1 cause of 1555 is committing in a cursor for loop. You are causing it yourself.

If you feel you truly must (and I am 100% of the opinion that you do not want to) commit, alter your logic from something like:


for x in ( select rowid r from t where <some condition> )
loop
delete from t where rowid = x.r;
cnt := cnt+1;
if ( cnt = 1000 ) then
commit;
cnt := 0;
end if;
end loop;


into:

loop
delete from t where <some condition> AND rownum <= 1000;
exit when sql%rowcount = 0;
commit;
end loop;
commit;


followup to comment two below


The second loop keeps NO cursors open across commits. That is why is avoids the 1555. It is a cursor opened against a table that you are modifying in a loop and you commit frequently that causes the 1555.

I said:

<quote>
The #1 cause of 1555 is committing in a cursor for loop. You are causing it yourself.
</quote>

Not:

the #1 cause of 1555 is commiting in a loop.

It is when you comitt across fetches that you do this to yourself.



followup to comment three

Yours is a different issue. Yous is the issue that:

"The probability of getting an ORA-1555 with your Query is directly proportional to the size of your SMALLEST rollback segment"


I really dislike the "have a big rollback segment" approach. I am a firm believe that all rollback segments should be created equal -- they should all be the same size.

You see -- the ORA-1555 you are getting is against the table with 2million records. You have other transactions modifying that table while you are reading it. They are modifying and commiting their changes. Lets say that at the BEGINNING of your run, some process updates the "last row" in the 2million row table and committs. Their rollback entry is free to be reused now if that rollback segment they put their UNDO in wraps around. You go about processing the table and eventually get to that last row. You need to do a consistent read on it -- you need their UNDO. Unfortunately, their UNDO went into a small rollback segment that wrapped a couple of times already. The UNDO you need is long gone, you get the ORA-1555.

For you, what you need to do, is to ensure that you have sufficient rollback pre-allocated in ALL rollback segments so as these rollback segments do NOT get overwritten during the course of your longest process.

In 9i, this might be a little easier to deal with as you can set an undo rentention period (however, you must be prepared to have the disk available to support it!)

My book goes into the ORA-1555 in some detail with examples showing how and when you will hit it as well.