Re: snapshot too old

Hello Daniel,

  I don not see relevance of your counter-advice in this case.

  Commit frequency is specific to the pattern of table dml access done by other concurrent users.

  You can not have silver bullet size, saying that 100 is good or 2,000 is good. I do try to have batch sizes as big as possible, but in one case was forced to commit every 10 records on very active system - anything else was casing problems.

  In lab you may run whole update as a one statement and be fine. In active DML environment you would need to commit frequently or face locking problems.

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1077813689.120776_at_yasure...
> Ron wrote:
>
> > Hello Shawn,
> >
> > Please check if below script helps:
> >
> > declare
> > counter number := 0;
> > cursor c1 is select /*+ parallel(y,2) full(y) */ rowid rid from
> > test_table y;
> > begin
> > FOR c2 in c1 LOOP
> >
> > update test_table
> > set SSN = TRANSLATE
> > ('915482376','123456789',TRANSLATE(SSN,'1234567890','9732581406'))
> > where rowid = c2.rid;
> >
> > counter := counter + 1;
> >
> > IF ( mod(counter,100) = 0)
> > THEN
> > COMMIT;
> > END IF;
> > END LOOP;
> > COMMIT;
> > END;
> > /
> >
> >
> > Regards,
> >
> > Ron
>
> Please go to:
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:9157869602021147219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7661190956484,
>
> and there you will note the following advice:
> ============================================
> Committing frequently will only:
>
> o increase the probability of an error
> o cause you to run slower
> o make your code more breakable
> ============================================
> And then stop giving out advice that is absolutely
> contrary to best practices.
>
> Thank you.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Thu Feb 26 2004 - 11:22:59 CST