Re: ORA-01555: snapshot too old

> <masseys_at_my-dejanews.com> wrote:
> >
> > 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

On Mon, 15 Feb 1999, Serge wrote:
>
> You need:
> 1. Create a big rollback segment - f.e. big_rs
> 2. Before copy set transaction - SET TRANSACTION USE ROLLBACK SEGMENT big_rs;
> Serge Malyshev sermal_at_usa.net

OK, that won't work. ORA-1555 results from the inability to obtain a consistent read, not from your session running ou of rollback space. Using SET TRANSACTION... for a query (which is not even a transaction, it is a query) won't do anything.

Understanding Oracle's multiversioning consistent read architecture is essential to understanding ORA-1555.

I wrote a blurb on how this works, and have posted it to the newsgroups several times. Rather than reposting it, here is the Dejanews URL:

http://www.dejanews.com/getdoc.xp?AN=402881062

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Mon Feb 15 1999 - 11:27:56 CST