declare 
  -- Local variables here
  
begin
  -- Test statements here
  for i in 1..10000 
  loop
    insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test;
  end loop;
  commit;
end;

------解决方案--------------------
declare  
 -- Local variables here
    
begin
 -- Test statements here
 for i in 1..10000  
 loop
   insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test;
 end loop;
  if i= 100000
  then
 commit;
  else null;
end;



------解决方案--------------------
你可以定个变量 查出一万提交一次  170万就是 170次   当变量等于 170的时候 就跳出循环

------解决方案--------------------
bulk collect加limit,再用forall

------解决方案--------------------
探讨

SQL code


declare 
  -- Local variables here
  
begin
  -- Test statements here
  for i in 1..10000 
  loop
    insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@tes……


------解决方案--------------------
8楼可以不过就是代码多慢。
  declare
  v_cmt_cnt number:=0;
  begin
  for c in(select rowid rid,rownum r from ch_flat@test)
  loop
  insert into ch_flat (colunm1,column2)  select column1,column2 from ch_flat@test where rowid=c.rid;
  if(mod(c.r,10000)=0)then
     commit;
     v_cmt_cnt:=v_cmt_cnt+1;
     end if;
    end loop;
   commit;
    dbms_output.put_line('commited times:'||v_cmt_cnt);
 end;

------解决方案--------------------
SQL code

declare
  n_count number:=0;
CURSOR c IS SELECT colunm1,column2 FROM ch_flat;

  begin
  for my_c in c
  loop
  insert into ch_flat (colunm1,column2) select my_c.column1,my_c.column2;
  n_count:=n_count+1;
  if mod(n.count,10000)=0 then
  commit;
  end if;
  end loop;
  commit;
  end;
我的异常网推荐解决方案：oracle存储过程,http://www.aiyiweb.com/oracle-develop/177537.html