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; PGA_USED_MEM ------------ 14928 SQL> / PGA_USED_MEM ------------ 15015
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