ORA-30009: Not enough memory for CONNECT BY operation

来源：互联网发布：淘宝食品类目规则编辑：程序博客网时间：2024/05/20 09:11

其实这个是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;

0 0