Re: ORA-01555 snapshot too old ...

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

        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 ;

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.