Re: Help: Snapshot Too Old

The problem may well be with the PL/SQL cursor. Don't forget that the cursor is stored in the rollback segment and is open for the whole 100m row transfer. If this is the case, you can either make a very big rollback segment for the cursor and use a SET TRANSACTION. Alternatively, you can try some fancy programming and actually close the cursor every so often - but that will slow the overall process because you've got to find your place again.

Len

>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 Fri Apr 13 2001 - 22:17:11 CEST