今天在搭建一个测试环境的时候，遇到了一个错误： ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key) 起初一看是认为imp失败，想重新导入，但是imp任务并没有停止，跳过error，继续导入后面的数据，我仔细一看, 原因是在CREATE INDEX的时候报的错，当create index的时候，是需要排序的, pga_aggregate_target>0是，启用pga自动管理，sort也由pga来自动分配，所以当sort_area_size被分配的大小>os 剩余内存，那么oracle会报这个错误， 这是我个人的理解： 报错信息如下： IMP-00017: following statement failed with ORACLE error 4030: "CREATE INDEX "FCBILLNOTICEBAKINDEX" ON "FC_BILL_NOTICE_BAK" ("ID" , "SEND_STATE" , "TRANSID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 201" "326592 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CPFINDX" LOGGING" IMP-00003: ORACLE error 4030 encountered ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key) IMP-00017: following statement failed with ORACLE error 20000: "BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"FCBILLNOTICEBAKINDEX"',NULL,NULL,NULL,2915265,11355,2915265,1,1,223495,2,6); END;" IMP-00003: ORACLE error 20000 encountered ORA-20000: INDEX "SYJZ"."FCBILLNOTICEBAKINDEX" does not exist or insufficient privileges ORA-06512: at "SYS.DBMS_STATS", line 2124 ORA-06512: at "SYS.DBMS_STATS", line 5473
查了一下metalink，相关解释如下： This error indicates that the oracle server process is unable to allocate more memory from the operating system. 这个错误暗示了ORACLE服务器进程从OS中，不能分配更多的内存。 The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform. its job. ORA-4030暗示了进程需要更多的内存(UGA和PGA)去执行他的工作。 1. Is there still sufficient memory available 查看OS是否还有充足的可用内存 top ：usually displays physical memory and swapspace statistics. swapon -s ：displays swapspace usage vmstat ： displays free physical memory
2. Is there an operating system limit set? 查看OS上时候做了相关内存使用资源的限制 ulimit -a
3. Is there an oracle limit set? 查看ORACLE 有无限制 PGA parameter implemented which limits the total amount of PGA that can be allocated for an instance select sum(value)/1024/1024 Mb from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and name = 'session pga memory';
4. Which process is requesting too much memory SQL> select sid,name,value from v$statname n,v$sesstat s where n.STATISTIC# = s.STATISTIC# and name like 'session%memory%' order by 3 asc;
5. How to collect information on what the process is actually doing a.select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = b. alter session set events '4030 trace name heapdump level 25' This dump can be used by Oracle Support analysts to find the cause of the excessive memory allocation.
6. General suggestions on avoiding this error a. some operations just require a lot of memory. For sort issues, decreasing SORT_AREA_SIZE can help.This means that lowering SORT_AREA_SIZE can have a performance impact on queries requiring huge sort operations. b. WORKAREA_SIZE_POLICY = AUTO & PGA_AGGREGATE_TARGET >0 c. Make sure your operating system and oracle limits are set reasonably d. Make sure there is enough memory available (physical memory and swapspace)
Starting with Oracle9i, an option is provided to completely automate the management of PGA memory. Administrators merely need to specify the maximum amount of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET. Furthermore, Oracle9i and newer releases can adapt itself to changing workload thus utilizing resources efficiently regardless of the load on the system. The alter system set WORKAREA_SIZE_POLICY = AUTO and alter system set PGA_AGGREGATE_TARGET = (>0 int)
In 9iR2, PGA_AGGREGATE_TARGET parameter controls the sizing of workareas for all dedicated server connections, but it has no effect on shared servers (aka MTS) connections and the *_AREA_SIZE parameters will take precedence in this case. In 10g, PGA_AGGREGATE_TARGET controls workareas allocated by both dedicated and shared connections. In 11g, Automatic Memory Management (AMM) expands to managing both SGA and PGA memory. Under memory pressure for PGA memory, SGA memory will be re-allocated for use by a process to accommodate workarea needs. NOTE: With AMM, setting an explicit value for PGA_AGGREGATE_TARGET will act as a minimum setting that AMM will not shrink below.
参考文献：1 .Subject: Diagnosing and Resolving ORA-4030 errors Doc ID: 233869.1 Type: TROUBLESHOOTING Modified Date : 05-FEB-2009 Status: PUBLISHED
I did a check on the database and found the Ora-04030 error.
ORA-04030: out of process memory when trying to allocate 4032 bytes (ioc_make_sub2,ioc_allocate_pal) ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError ORA-04030: out of process memory when trying to allocate 16640 bytes (joxcx callheap,ioc_allocate free)
Cause: Operating system process private memory has been exhausted.
Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.
I just flushed the shared pool . Do you encounter this error again ?
NAME VALUE UNIT ---------------------------------------------------------------- ---------- ------------ aggregate PGA target parameter 0 bytes aggregate PGA auto target 0 bytes global memory bound 0 bytes total PGA inuse 1.2912E+10 bytes total PGA allocated 1.7236E+10 bytes maximum PGA allocated 2.0533E+10 bytes total freeable PGA memory 0 bytes PGA memory freed back to OS 0 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 0 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 0 bytes extra bytes read/written 0 bytes cache hit percentage 0 percent