昨天有个同事反映，代码执行反馈ora04030错误:

这个错误一般是pga内存不够导致的，错误信息:

ora04030:out of process memory when trying to allocate 16408bytes。。。

我们新建一个会话，执行一个存储过程:

DECLARE
  LN_RETURNVALUE NUMBER;
BEGIN
  LN_RETURNVALUE := 0;
  OSS_PKG_MAIL_KEYWORD1.P_TRANS_MAIL_KEYWORD(TO_DATE('20100601', 'yyyymmdd'),
                                            TO_DATE('20110721', 'yyyymmdd'),
                                            1,
                                            '执行用户邮件关键字统计',
                                            LN_RETURNVALUE);
END;

查找出sid，然会对应查询v$process_memory:

SELECT pid,serial#,CATEGORY,allocated/1024/1024 allo_MB ,used/1024/1024 used_MB,max_allocated/1024/1024 MAX_MB
  FROM V$PROCESS_MEMORY
 WHERE PID = (SELECT P.PID
                FROM V$SESSION S, V$PROCESS P
               WHERE S.PADDR = P.ADDR
                 AND S.SID = 52);   5分钟之后 看如下几列

pid seria, category , allo_MB   MAX_MB

37    SQL                         0             1
37   PL/SQL                   1              1
37    Other                       8192     8192；

其中分配内存和，最大内存不断暴涨，最终用光内存报错；当然这里写的8G不全是内存，观察操作系统使用内存使用是从4G到7.7G，pga一直在增长，最终达到4G

select  b.*,VALUE/1024/1024/1024 VALUE from v$sesstat a,v$statname b
              where a.statistic#=b.statistic# and  a.sid=120

4 26 session pga memory max 1 507777907 3.99997290223837;

原代码如下:

OPEN cur_mail_keyword_stat FOR lv_execsql USING pi_begindate,
                                                     pi_enddate;
        po_returnvalue := 104;
        LOOP
             FETCH cur_mail_keyword_stat BULK COLLECT
                INTO table_mail_subject,
                     table_user_id,
                     table_access_time
                LIMIT 1000;
            
             --po_returnvalue := 105;
             FORALL i IN 1 .. table_mail_subject.count
                    INSERT INTO a

                        (stat_date,
                         user_id,
                         keyword_id,
                         keyword,
                         total_count)
                        SELECT to_number(TO_CHAR(table_access_time(i),'yyyymmdd')),
                               table_user_id(i),
                               a.keyword_id,
                               a.keyword,
                               1
                        FROM    a
                        WHERE  instr(table_mail_subject(i), a.keyword) > 0
                        AND    a.keyword_type = 'mail'
                        AND    a.state=1;
                   
             COMMIT;
             --po_returnvalue := 106;       
                     
        END LOOP; 
        CLOSE cur_mail_keyword_stat;

这里主要的错误是关联数组没有退出条件在end loop前加一个退出条件即可:修改如下:

 COMMIT;
             --po_returnvalue := 106;       
          EXIT WHEN cur_mail_keyword_stat%NOTFOUND OR cur_mail_keyword_stat%NOTFOUND IS NULL;                  
        END LOOP; 
        CLOSE cur_mail_keyword_stat; 

重新测试，一切正常。