Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: snapshot too old
Hello Shawn,
Please check if below script helps:
declare
counter number := 0; cursor c1 is select /*+ parallel(y,2) full(y) */ rowid rid fromtest_table y;
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;
Regards,
Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html
"Shawn" <spedwards_at_qwest.net> wrote in message
news:yPe%b.576$os4.58426_at_news.uswest.net...
> 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;
>
>
Received on Thu Feb 26 2004 - 03:11:39 CST