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

Sybrand Bakker wrote:
> On 12 Sep 2006 07:56:22 -0700, spremuta_at_gmail.com wrote:
>
> >do i have to change script in the lines:
> >IF CONTATORE_COMMIT = FREQUENZA_COMMIT
> > > THEN CONTATORE_COMMIT := 1;
> > > COMMIT;
> > > END IF;
> >?
> >deleting only commit line or all that lines in your opinion?
>
> 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. :) 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. Rewriting this PL/SQL code into single SQL INSERT statement will.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Tue Sep 12 2006 - 15:01:11 CDT