Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: snapshot too old

Re: snapshot too old

From: Ron <support_at_dbainfopower.com>
Date: Thu, 26 Feb 2004 01:11:39 -0800
Message-ID: <meGdnelhrOYyK6DdRVn-hA@comcast.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US