Re: ORA-01555: snapshot too old

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 17 Feb 1999 13:59:29 GMT
Message-ID: <36caca65.5959008@192.86.155.100>

A copy of this was sent to "Chris Viljoen" <viljocp_at_ch.etn.com> (if that email address didn't require changing) On 16 Feb 1999 22:07:57 GMT, you wrote:

>Here is why you get snashot too old error.
>
>Suppose at time t1 user u1 starts an update to table TAB1, so it grabs a
>rollback segment r1. This is a large update so it is going to take some
>time.
>
>Now at time t2, user u2 starts selecting from this table. u1 has not yet
>commited so, u2's process will use r1 to create an image of TAB1. u2's
>query is also very long.
>
>Now at time t3 (where t1<t2<t3), u1 commits so it releases the rollback
>segment r1 for all other updates to database. And at t4(where t4<t3) the
>rollback segment wraps around. But u2's query is not yet finished. And some
>other user process grabs the r1 for its own transaction. And at this point
>u2 cannot get the snapshot of the TAB1 at t2. So the error "snapshot too
>old" comes.
>
>I hope this clears why the error comes. The solution would be to create a
>large rollback segment and use it for a large transaction like urs. Or do
>frequent commits.
>

You got the example right and the first part of the solution right but the advice:

"or do frequent commits"

is EXACTLY what is causing the problem below. We have a user reading and writing to the SAME table. Since they are committing frequently -- they are causing the 1555 to happen to themselves.

the solution should be:

"create large rbs and use it and DO NOT commit -- the commit is causing the 1555".

the most frequent cause of the 1555 is commiting inside of a cursor for loop that is reading the table in the loop and modifying the table inside the loop. The sqlplus copy command they are doing below is *very* similar to:

  for x in ( select a.*, rownum rnum from postcode )   loop

     insert into postcode values ( x.c1, x.c2, ... );
     if ( mod( x.rnum, 1000 ) = 0 ) 
     then
        commit;
     end if;

  end loop;

its that commit that is killing them.

>Ketan.
>
>Oracle7 certified dba.
>Cutler Hammer.
>
>masseys_at_my-dejanews.com wrote in article
><7a8q9v$45r$1_at_nnrp1.dejanews.com>...
>> Hi,
>>
>> I am trying to copy a large table from one user to another using the
>commands
>>
>> set copycommit 1
>> set arraysize 1000
>> COPY FROM <us>/<pw>@<cs> append POSTCODE using select * from POSTCODE;
>>
>> and I get an error lien stating
>>
>> ORA-01555: snapshot too old: rollback segment number 5 with name "RB4"
>too
>> small
>>
>> What does this mean and how do I fix it.
>>
>> FYI the database I am copying from has 1.55 million records and the above
>> command
>> fails after 1.35 million records.
>>
>>
>> Thanks
>>
>> -----------== Posted via Deja News, The Discussion Network ==----------
>> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 17 1999 - 07:59:29 CST