ORA-04036 PGA memory exceeds PGA_AGGREGATE_LIMIT

Although the PGA_AGGREGATE_LIMIT is set and Oracle decided to kill the process/session that was using so much, it sometimes still exceeds and grows and eventually exhausting all the memory.

SQL> show parameter pga_aggregate_limit
NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
pga_aggregate_limit             big integer 14G

SQL> select round(pga_used_mem/1024/1024) PGA_USED_MEM
from v$process p join v$session s on p.addr = s.paddr
where s.sid = 259;


SQL> /


From the alert log:

KILL SESSION for sid=(259, 29568):
   Reason = alter system kill session
   Mode = KILL HARD SAFE -/-/-
   Requestor = USER (orapid = 25, ospid = 4952, inst = 1)
   Owner = Process: USER (orapid = 148, ospid = 5628)
   Result = ORA-31

The culprit here is the ORA-31 error. This is the ‘marked for kill’ message. So the process is not killed, but only marked for kill:

Continue reading