Re: ORA-01555 snapshot too old ...
Date: 1995/09/19
Message-ID: <43mcb4$b8_at_tpd.dsccc.com>#1/1
The size of the row being updated impacts how much of the rollback log is used. Here a section of code I had to use to get out of the problem.
/************************************************************** * * set the OCC crt software load number and item name number * We have to update more then 1,700,000 rows. * * commit change every 50,000 records to prevent * ORA-01555 snapshot too old * **************************************************************/BEGIN
Declare
row_cnt number := 0 ; row_id rowid ; cursor srlexp is select rowid from srlexp_sli where pk_product = 'OCC' ; begin open srlexp ; fetch srlexp into row_id ; while (srlexp%FOUND) loop row_cnt := row_cnt + 1 ; update srlexp_sli sli set sli.seqnum = (select seqnum from srlexp_pkg where pk_product = sli.pk_product and pk_rec_key = sli.pk_key ) where rowid = row_id ; if mod (row_cnt, 50000) = 0 then commit ; end if ; fetch srlexp into row_id ; end loop ; commit ; /* any remaining rows */ close srlexp ;
end ;
END ;
/
Timothy Dry (tdry_at_sierra.net) wrote:
:> I'm receiving this error and I can't really figure out why.
:> The ORACLE Messages and Codes manual simply says to increase
:> rollback space. I really don't think this is the problem (although
:> it may solve it in the interim).
:> My application is reading a number of rows (50,000) from a single
:> table and creating new records in other tables and updating the
:> read record. I've allocated what I consider more than adequate
:> rollback space for this, yet I still get this error. In addition, this
:> application is running in it's own instance and no other users are
:> connected while it is running. Thanks in advance for any advice.
:> Tim Dry
:> tdry_at_sierra.net
-- This posting represents the personal opinions of the author. It is not the official opinion or policy of the author's employer. Warranty expired when you opened this article and I will not be responsible for its contents or use.Received on Tue Sep 19 1995 - 00:00:00 CEST