ORA-04030: out of process memory when trying to allocate 123416 bytes

ORA-04030: out of process memory when trying to allocate 123416 bytes
====================================================

we revive alert from database due to less memory. user run query consuming more.


It impacts other process to fail. This error causing lack of memory in pga and makes other process to fail.

You can also monitor the PGA usage and high consumption of the pga process with below queries during the error time.

=================================================

spool memory_results.txt append

set pages 999
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;


PROMPT
PROMPT PROCESS CONSUMING MORE PGA:
PROMPT -------------------------

set line 200
col logon for a10
col spid for a10
col username for a10
col osuer for a10
col machine for a10
col program for a15
col module for a10
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$session.SERIAL#, v$process.SPID , ROUND(v$process.pga_used_mem/(1024*1024), 2) PGA_MB_USED,
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--and status = 'ACTIVE'
--and v$session.sid = 97
--and v$session.username = 'SYSTEM'
--and v$process.spid = 24301
ORDER BY pga_used_mem DESC;

PROMPT
PROMPT List largest process:
PROMPT -------------------------

col user for a20
SELECT pid, spid, substr(username,1,20) "USER", program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
FROM v$process
WHERE program NOT LIKE '%LGWR%');

PROMPT
PROMPT Summation of PGA based on v$process:
PROMPT -------------------------

SELECT SUM(pga_alloc_mem)/1024/1024 AS "Mbytes allocated", SUM(PGA_USED_MEM)/1024/1024 AS "Mbytes used" FROM v$process;

PROMPT
PROMPT Summation of PGA memory based on V$SESSTAT:
PROMPT -------------------------

SELECT SUM(value)/1024/1024 AS Mbytes FROM v$sesstat s, v$statname n WHERE n.STATISTIC# = s.STATISTIC# AND n.name = 'session pga memory';

PROMPT
PROMPT PGASTATS
PROMPT -------------------------

SELECT SUBSTR(name,1,30), value, unit FROM v$pgastat;

PROMPT
PROMPT List all processes including PGA size from V$PROCESS.
PROMPT -------------------------

SET LINESIZE 120
SET PAGESIZE 120
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 99,999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 99,999,999,999
COLUMN username HEADING 'oracleuser' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a20

SELECT p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
p.pga_used_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
AND p.background is null /* Remove if need to monitor background processes */
ORDER BY p.pga_alloc_mem DESC;



PROMPT
PROMPT Summation of PGA and SGA
PROMPT ----------


SELECT SUM(bytes)/1024/1024 AS Mbytes
FROM (SELECT value AS bytes
FROM v$sga
UNION ALL
SELECT value AS bytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory');



PROMPT
PROMPT PGA and SGA Memory hourly
PROMPT -----------------


SELECT dhs.instance_number, trunc(dhs.end_interval_time,'mi') Time,
pga.mem pga, sga.mem sga,(pga.mem+sga.mem) Total_MB
FROM
(SELECT snap_id,instance_number,round(sum(value)/1024/1024,4) mem
FROM DBA_HIST_PGASTAT
WHERE name = 'total PGA allocated'
GROUP BY snap_id,instance_number) pga,
(SELECT snap_id,instance_number,round(sum(bytes)/1024/1024,4) mem
FROM DBA_HIST_SGASTAT
GROUP BY snap_id,instance_number) sga,
dba_hist_snapshot dhs
WHERE dhs.snap_id=sga.snap_id
and dhs.instance_number=sga.instance_number
and dhs.snap_id=pga.snap_id
and dhs.instance_number=pga.instance_number
ORDER BY dhs.snap_id desc, dhs.instance_number

spool off
===================================================================