Re: ORA-01555: snapshot too old

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.

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
 

>
Received on Tue Feb 16 1999 - 16:07:57 CST