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


spremuta@xxxxxxxxx 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

.

Relevant Pages

  • Re: -1555-ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" t
    ... it happen to fetch something from a big result set in a loop and commit ... Or does it happen that the query runs longer than ...
    (comp.databases.oracle.server)
  • Re: Where should I put the loops
    ... Thank you very much for the neat script. ... But I know if the page doesn't exit that Ruby will fetch the ... same papge again and again until it comes to the upper limit of loop. ... order to break out of the loop what I do is that I compare the website ...
    (comp.lang.ruby)
  • Re: What is wrong with this????
    ... cursor c_triggers is ... fetch c_triggers into v_trgname; ... end loop; ... As you can see there is no rocket science in the script. ...
    (comp.databases.oracle.server)
  • Re: While Loop Issues
    ... the error "Fatal error: Cannot access empty property in fb2phpbb2.php ... on line 146" If i comment out the loop. ... The script does populate the ... code that are much more complex that I simply cant update the query ...
    (comp.lang.php)
  • Re: What is wrong with this????
    ... cursor c_triggers is ... fetch c_triggers into v_trgname; ... end loop; ... As you can see there is no rocket science in the script. ...
    (comp.databases.oracle.server)