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

spremuta_at_gmail.com wrote:
> xhoster_at_gmail.com ha scritto:
>
> > "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.
> >
>
>
>
> I don't know if an Insert is more efficient than a Fetch regarding
> tables that contains 80-90 milions of records, i haven't create the
> pl/sql script i posted, but i can change it if surely an insert is
> better...

Take Vladimir's advice in this thread and fix the code as suggested. Received on Wed Sep 13 2006 - 14:26:07 CDT