RESTRICTING PGA SIZE

ONLINE RENAME AND RELOCATION OF AN ACTIVE DATA FILE, AskHareesh.blogspot.com
RESTRICTING PGA SIZE
Pre Oracle 12c R1, there was no option to limit and control the PGA size. Although, you set a certain size toPGA_AGGREGATE_TARGET initialization parameter, Oracle could increase/reduce the size of the PGA dynamically based on the workload and requirements. In 12c, you can set a hard limit on PGA by enabling the automatic PGA management, which requires PGA_AGGREGATE_LIMIT parameter settings. Therefore, you can now set the hard limit on PGA by setting the new parameter to avoid excessive PGA usage.
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; --disables the hard limit

When the current PGA limits exceeds, Oracle will automatically terminates/abort the session/process that holds the most untenable PGA memory.

*/