ORA-30009: Not enough memory for CONNECT BY operation

其实这个是sga内存不足，ok，是这样的。但是不要一味的加大内存，先看看你的程序，一定要把内存占用完，直到报错，才提交？

所以可以直接修改提交的频次。 这样把数据存物理盘，就不会出现这样的问题

insert 未提交，就在buffer cache或data file（undo）。

出错前

     
declare
   v_commit int ;
BEGIN
  v_commit:=0;
  
  FOR cur IN (  
    select * from (
     select startip,endip,endip-startip as jk,country,province,city,mno   from cfg_globalipseg_0726 where startip<>endip 
     and startip in (select startip from ml_456) 
     ) where jk>100000  and jk<=200000
              
               )
    LOOP
      BEGIN
        insert into cfg_globalipseg_0801_zk_2
         select cur.startip +rownum-1 as startip
                ,cur.startip +rownum-1 as endip
                ,cur.country  
                ,cur.province
                ,cur.city
                ,cur.mno
             from dual  connect by level <= cur.jk+1;
      v_commit:=v_commit+1;
      
      if mod(v_commit,1000)=0 then
        commit;
      end if;
      
      END;
    END LOOP;
    commit;
END;

 

修改后，不出错

     
declare
   v_commit int ;
BEGIN
  v_commit:=0;
  
  FOR cur IN (  
    select * from (
     select startip,endip,endip-startip as jk,country,province,city,mno   from cfg_globalipseg_0726 where startip<>endip 
     and startip in (select startip from ml_456) 
     ) where jk>100000  and jk<=200000
              
               )
    LOOP
      BEGIN
        insert into cfg_globalipseg_0801_zk_2
         select cur.startip +rownum-1 as startip
                ,cur.startip +rownum-1 as endip
                ,cur.country  
                ,cur.province
                ,cur.city
                ,cur.mno
             from dual  connect by level <= cur.jk+1;
      v_commit:=v_commit+1;
      
      if mod(v_commit,10)=0 then
        commit;
      end if;
      
      END;
    END LOOP;
    commit;
END;

 

--其实就是修改了，commit 频次。

 if mod(v_commit,1000)=0 then
        commit;
      end if;