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

What am I missing? Does this not devolve to a simple Update? Why is this being done in a procedure?

In any case, the Action states 'Use larger rollback segments'. Try that! 1.5M rows is not a lot so I suspect you have itty-bitty rollback segments. Suggest you grow them all.

BTW: Having a version number and OS would be nice.

/Hans Received on Thu Feb 26 2004 - 00:27:04 CST