Hello,
While executing an alter table to include two varchar2 columns (with sizes of 200 and 500 bytes), the wait event for the instance session was always "PGA Memory operation" and its pga consumption reached more than 30GB after 1h of execution.
Table has about 670MB and only date, number and varchar2 columns.
When the session was killed, these errors appeared:
ORA-00603: ORACLE server session terminated by fatal error
ORA-04030: out of process memory when trying to allocate 592 bytes
(callheap,kfmditer)
ORA-04030: out of process memory when trying to allocate 592 bytes
(callheap,kfmditer)
ORA-04030: out of process memory when trying to allocate 536 bytes
(callheap,kcbtmal allocation)
ORA-00604: error occurred at recursive SQL level 1
ORA-04030: out of process memory when trying to allocate 216 bytes
Environment:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Red Hat Enterprise Linux Server release 7.6 (Maipo)
SYS@haprod> show parameters pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 40G
pga_aggregate_target big integer 20G
[oracle@ha2114 (haprod) ~]$ free -m
total used free shared buff/cache available
Mem: 128640 68750 992 3503 58897 54931
Swap: 16399 0 16399
[oracle@ha2114 (haprod) ~]$ ps -ef | grep lgwr
oracle 11493 7846 0 04:17 pts/0 00:00:00 grep --color=auto lgwr
oracle 22790 1 0 2019 ? 05:45:28 ora_lgwr_haprod
oracle 26825 1 0 2019 ? 00:03:59 ora_lgwr_ha2prod
oracle 64801 1 0 2019 ? 00:06:16 ora_lgwr_ha1049
This same alter table was executed in a dev environment with success.
Anyone dealed with this kind of problem before or applied the sugestions in 2138882.1 in a similar situation?