Re: ORA-1555 Snapshots Too Old Error[Q]

MarkP28665 (markp28665_at_aol.com) wrote: : Regards ==> ORA 1555 and Rollback Segment setup

I had this problem under v6, but I think it works the same. Oracle stores an image of the data being deleted such that other users can still see a complete image of the table until a commit occurs. It does this in the rollback segments. If the body of data being deleted is bigger than the rollback it tries to re-use extents that it has already written to.

The easiest way around it is to commit after every 'N' rows where n is a suitable number of rows that can be committed without rollback problems. This will not work if you have an outer driving cursor and are deleting records inside the loop and commiting regularly. The outer cursor tries to keep visibility of the same image it had when it was opened.

An easy (but slow) way to do this is use rownum in your delete statement i.e:

delete from <table>
where <where clause>
and rownum < n;

Just loop this and do it until there are no more rows on the table and you should be right.

Hope this helps, Received on Tue Jun 13 1995 - 00:00:00 CEST