Re: -1555-ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small

xhoster_at_gmail.com wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> wrote:
> > Sybrand Bakker wrote:
> > >
> > > ALL that lines.
> > > Better still : get rid of the entire for loop (as it fetches record by
> > > record) and convert this mess into a proper
> > > INSERT
> > > SELECT statement
> > >
> > > and forget about it.
> > >
> >
> > Couldn't say better. :)
>
> Me neither.
>
> > Not only will this get rid of ORA-1555, but you
> > will find that the whole process completes faster and uses less
> > resources. My suggestion about changing undo_retention was made in
> > assumption that you do NOT commit inside the loop over a cursor, which
> > you *never* should do in Oracle unless you want to make sure you will
> > get ORA-1555 at some point. Changing undo_retention will not help in
> > this case.
>
> Why won't it help in this case (other than for the reason that once the
> obvious change is made there would no longer be a need for help)?
> Committing in a fetch-loop is surely an effective way to stomp on redo
> which you will later need, but I see no reason to think it is qualitatively
> different than all other methods of achieving the same inglorious result.
>
> In fact, I've seen 1555's disappear merely by changing undo_retention in a
> situation (unlike this one) where it couldn't easily be rewritten as an
> insert into...select and where removing the commit from the loop would have
> been rather inconvenient.
>

Well, I didn't mean it won't make 1555 go away (though that's not guaranteed anyway,) but associated price is too high while fixing the code in this particular case is cheap and efficient. Besides, committing in a fetch-loop forces you to code for incomplete processing. That is, you need to think about, and code for, situations when the loop is broken halfway for any reason, from an unexpected error to deliberate session kill, and you now have part of your data processed (and committed!) and part not - how do you restart the process so that it picks up where it left? This means more work for you implementing and for Oracle executing.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Sep 13 2006 - 06:35:21 CDT