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

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... Received on Wed Sep 13 2006 - 03:45:54 CDT