Re: Snapshot Too Old

If your selection logic is simple - then I'd suggest you look into the SQL*Plus command COPY. This will perform the required COMMITs as it goes and has never given me a problem with snapshot too old.

Import/export is also not a bad idea - however COPY doesn't require the additional disk space.

If you need to include complex logic (ie wish to use PL/SQL) there are ways to do that also - but the explanation is slightly longer.

Cheers,

Richard Gowan

<u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.987089246.1892639160_at_[198.138.198.252]...
> I have to move 100m rows to another table
> use select/insert in PL/SQL. We have 10 rollback
> segments and a huge RBS tablespace, plenty
> room for rollback segment to grow.
>
> At first, we try to commit every 200 rows, and
> we always got the error message snapshot too
> old after about 200,000 rows, we begin to reduce
> the times of commit,commit every 2000, 20,000 and
> 200,000 and now 2,000,000 rows. It is still running
> and it takes a long time to know whether it will work
> or not.
>
> Could somebody tell me why commit too often will
> also cause the error snapshot too old?
>
> How do you decide how many rows to commit?
>
> Thank you very much for your help.
>
>
>
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail subpart of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Wed Apr 18 2001 - 12:42:31 CEST