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

spremuta_at_gmail.com wrote:
> Vladimir M. Zakharychev ha scritto:
>
> > And what the application is doing when you're getting the error? Does
> > it happen to fetch something from a big result set in a loop and commit
> > inside this loop? Or does it happen that the query runs longer than
> > specified undo retention period (10800 seconds is 3 hours,) while
> > there's a lot of transactional activity in the db?
> >
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com
>
>
> Is there a fetch into a cursor declaration, in a sql script that
> somethimes is long 4-6 hours....
> in a table there are about 90milions of records.....

Well, if this script receives ORA-1555 on regular basis, then you should increase undo_retention to cover the longest run time period of this query so that Oracle retains undo needed by it for the whole duration of the process (say, undo_retention=22000.) Or better try to optimize that script so that it takes less time to complete, ideally less than 3 hours.

Hth,

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