ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Version:

DB: 12.1.0.1.0 EE

Issue:

Errors are obtained while running migration script in a 12C DB and the sessions are getting killed.

ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB

Reason:


From Oracle 12C, a new parameter called PGA_AGGREGATE_LIMIT sets a hard limit on the amount of PGA taken up by an instance.

The parameter pga_aggregate_limit, if not explicitly defined, defaults to the greater of these three numbers:
a) 2 GB
b) 3 MB times the value of the processes parameter
c) 2 times the value of the parameter pga_aggregate_target

SQL> show parameter pga_ag

NAME                                            TYPE                        VALUE
------------------------------------ -----------       ------------------------------
pga_aggregate_limit                       big integer                       2G
pga_aggregate_target                     big integer                       764M

When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session.

The error can be noticed in the alert log file and the incident would be reported in a trace file, where the current offending SQL can be found.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: /u01/app/diag/rdbms/orcl/orcl/incident/incdir_24367/orcl_ora_13239_i24367.trc

In trace file

----- Current SQL Statement for this session (sql_id=2fsstt9a4tqz7) -----


Solution:

One way is to go back to the previous (pre-12c) behavior. To do that set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.

alter system set pga_aggregate_limit=0 scope=both;

Obviously another option is to set this value to a much higher value if you have enough physical memory in the server

alter system set pga_aggregate_limit=3072M scope=both;