| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Fetch to copy tables by block
Thomas Kyte wrote:
>
> 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
Hello Thomas,
In SQL Plus, 'arraysize' gives the nice "block fetch" for lack of a better term...
Is this preserved across a database link ???
Namely, is there any difference is issuing:
set arraysize 100
insert into table1
select * from table2_at_other_site;
versus
set arraysize 5
insert into table1
select * from table2_at_other_site;
(and ditto for create as select etc )
Cheers
Connor
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 16 1999 - 06:30:50 CDT
 |
 |