ORA-04036: PGA memory used by the instance exceeds PGA AGGREGATE LIMIT

ORA-04036: PGA memory used by the instance exceeds PGA AGGREGATE LIMIT

PGA_AGGREGATE_LIMIT is used from Oracle 9i with automatic management of the PGA. It specify as SOFT LIMIT in Oracle 9i but from Oracle 12c it becomes the HARD LIMIT. In Oracle 9i it provide more memory if needed but it will get error in Oracle 12c if limit exceeds.

Error

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

Default Setting
Default setting of PGA in Oracle 12c

Connected.
SQL> show parameter pga

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

Solution
If you face the error ORA-04036 then you will increase the value of PGA_AGGREGATE_LIMIT parameter. It is dynamic parameter you can increase the value.
But you cannot decrease the value below the PGA_AGGREGATE_TARGET.

--Check PGA parameter value
SQL> show parameter pga

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

-- Increase the value to 6G
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=6G SCOPE=BOTH;

Disable parameter for getting rid of error ORA-04036
Note: You can use old behavior as used in Oracle 9i by disable it to set the value ZERO with SOFT LIMIT. It will disable the error permanently.

ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.