Re: ORA-01555

On Fri, 06 Feb 1998 11:28:02 -0500, "%username%" <duong.liem_at_ic.gc.ca> wrote:

>Hope someone can help us on this.
>
>We are running a SQR program on the UNIX server that updates the value
>of Emplid's on the TEST database.
>
>For each emplid on the database, the program will perform over 300
>SELECT statements to verify if there is any data for the old emplid. If
>so, it will build an UPDATE SQL statement to change the value to the new
>emplid. In average, the program will update about 13 tables.
>
>Once all the updates done for that employee, the program issues a COMMIT
>STATEMENT. Then it does the above process for the next employee.
>
>The program stops with the error message ORA-01555: snapshot too old
>(rollback segment too small).
>
>Any help is apreciated.
>
>Liem Duong
>PeopleSoft Project
>Industry Canada
>duong.liem_at_ic.gc.ca

Hi,

I suppose that you're doing all this in any kind of cursor loop. If I'm right on this, then a possible solution to the ORA-01555 would be not to commit inside this loop, as this ends your transaction and thus marks the blocks in the rollback segment, which provide the read consistent image to your cursor, as inactive. So these blocks get overwritten by the rollback data of your new transaction. Just using bigger rollback segments would only postpone the problem until to the point where the data quantity you process gets bigger again.

HTH
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net