Re: snapshot too old problem

From: Bippie <no.spam_at_for.me>
Date: 2 Jun 1998 15:33:51 GMT
Message-ID: <6l160v$qgm$1@hdxf08.telecom.ptt.nl>

iancrozier_at_aol.com (Iancrozier) schrijfbewerkingen: > When running a program to change certain values in an Oracle table, I get the
> following message after about 3000 rows have been updated:-
> 0RA-01555; snapshot too old: rollback segment number 1 with name "R05"
>
> We have tried increasing the size of our rollback files, but we still get this
> problem.
> Anyone have any suggestions? TIA

I've had the same problem some time ago. It seems like you are using a lot of cursors, This was what caused trouble in my case at least.

Statements like:

For cursor_rec in Some_cursor Loop

   update some_table

      set some_field = some_value

   where A = cursor_rec.X 
     and b = cursor_rec.Y
     and c = cursor_rec.Z

end loop

are best rewritten in something like (where possible):

update some_table
  set some_field = some_value <---You can use a sub-query here) where (A ,B , C) IN (select X, Y, Z

                     from some_table
                     where...);

The sub-query after the IN keyword should replace the cursor. This solved my problem and I think it's more efficient and better structured too !

If you do not understand exactly what I mean please post (Part of) your procedure, so I can take a more detailed look.

Hope this will help you   Received on Tue Jun 02 1998 - 10:33:51 CDT