--I guess not, bcoz I understand that when the cursor is opened, the query executes and the result set is moved to PGA & cursor fetch operation will be no more the dependent on the table/rollback segment to get the records.

I think it arises due to the high frequency of commits and delayed block clean out process, which makes the update statement not to get the consistent view.--Is my understandin correct...?

Does this process(as below steps) helps in avoiding the ORA-1555...? 1.Bulk fetching the cursor to collection 2.Close cursor immediately 3.Doing DML's looping through the collection rather that the cursor.

Re: Does ORA-1555 (snapshot too old) error occurs because of keeping the cursor open for long..?

Well sorry to contradict,but a 1555 is all about a cursor being open "too long" (whatever this means in terms of micro-seconds) and concurrent commits.

The result set is not always beinig moved anywhere. Just in some cases (i.e. sorts) a copy to a temp area is done. The normal case is, that the cursor simply proceeds when fetches occur.

open cursor stores the current scn

fetch goes ahead and reads data

if fetch finds a block that has a scn being higher than the one of open cursor (means it has been modified), it goes to undo, find the required related undo block with a scn equal or next lower of the one from "open cursor" and gets data from this undo block.-> If this is not possible, a 1555 will be thrown, no matter if classic rollback or new undo is in place.

Of course with new undo management chances are far better not to hit this pitfall, because automatic undo is able to utilize the entire UNDO space to avoid this as long as possible, while with classic rollback you'll need to deal with the resources of a single rollback segment, which might even not been related to your own transaction.

So the reason for 1555 is that the required resultset of a cursor is modified and committed (otherwise the required information can not get lost! If uncommited, it would ALWAYS be retrievable from UNDO) while the result is fetched AND (!) the related UNDO information for the timestamp of open cursor is no longer accessible to give you a consistent cursor.

For your problem, I'd recommend to use a cursor for update and a single commit at the very end.