Re: Fetch to copy tables by block

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 16 Sep 1999 10:22:27 -0400
Message-ID: <s=zgN13vGWqIMcevRqYRtiNaRxi6@4ax.com>

A copy of this was sent to laurent_pele_at_my-deja.com (if that email address didn't require changing) On Thu, 16 Sep 1999 13:46:32 GMT, you wrote:

>Hello everybody
>
>I want to copy a table into another one by blocks of 1000 rows to avoid
>exceeding the rollback segments size.
>
>I know how I can do that with Sybase : I write a stored procedure
>with a loop that fetch a group of 1000 rows but I haven't found the
>equivalent with Oracle.
>
>Can anybody may be of any help ?
>Thank you

check out the sqlplus copy command -- it'll copy a table and you can set the commit count to N to tell it to commit every N fetches of M rows (m = arraysize).

Also, something like:

  for x in ( select * from T ) loop

     insert into T2 values ( ... );
     cnt := cnt +1;
     if ( mod(cnt,1000) = 0 ) then commit; end if;

  end loop;

will do it (although the sqlplus copy command will be *much* faster.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 16 1999 - 09:22:27 CDT