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

"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.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB