ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)

Error in Alert log file

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p005_8232.trc  (incident=200692):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p006_8236.trc  (incident=200701):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 17 10:20:40 2016


Error In trace file

oracle@server1$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_8222.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_8222.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2
System name:    SunOS
Node name:      server1
Release:        5.10
Version:        Generic_150400-20
Machine:        sun4u
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 200
Unix process pid: 8222, image: oracle@server1 (P000)


*** 2016-05-17 10:20:40.630
*** SESSION ID:(60.7937) 2016-05-17 10:20:40.630
*** CLIENT ID:() 2016-05-17 10:20:40.630
*** SERVICE NAME:(SYS$USERS) 2016-05-17 10:20:40.630
*** MODULE NAME:(SQL Developer) 2016-05-17 10:20:40.630
*** ACTION NAME:() 2016-05-17 10:20:40.630

DDE: Problem Key 'ORA 4030' was flood controlled (0x4) (incident: 200613)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
DDE: Problem Key 'ORA 4030' was flood controlled (0x6) (incident: 200614)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)

*** 2016-05-17 10:20:42.310
DDE: Problem Key 'ORA 4030' was flood controlled (0x6) (incident: 200615)

ORA-04030: out of process memory when trying to allocate  bytes (,)


Solution

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later

Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]

It is a bug in oracle 11g version . To  resolve this issue set the value of  _PGA_MAX_SIZE to a lower value which internally forces the hash-join to use the less memory and avoid the ORA-4030

By default it is 200 MB.

SQL> alter system set _pga_max_size=120M;