Re: snapshot too old

Shawn wrote:

> Hi,
>
> I've been struggling to update this test table that has about 1.5 million
> rows in it. I'm getting this error message:
>
> 01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\"
> too small"
> // *Cause: rollback records needed by a reader for consistent read are
> // overwritten by other writers
> // *Action: Use larger rollback segments
>
>
> Below is my script. Any suggestions would be greatly appreciated.
>
> --
> declare
> counter number := 0;
> cursor c1 is select ssn from test_table;
>
> begin
> FOR c2 in c1 LOOP
> update test_table
> set SSN = TRANSLATE ('915482376','123456789',TRANSLATE(SSN,
> '1234567890','9732581406'))
> where ssn = c2.ssn;
>
> counter := counter + 1;
>
> IF (counter = 2500)
> THEN
> COMMIT;
> counter := 0;
> END IF;
> END LOOP;
>
> COMMIT;
> END;
> /
> exit;

Drop the counter and get the incremental commit out of it that is 90% of the problem. One commit after END LOOP is all you should have. If the other 10% still causes this to happen your rollback segments are too small for your transaction ... make them ALL larger. Do not use SET TRANSACTION.

-- 
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)