This post is in reference to one of the questions that a client DBA had asked recently. The question was “Whether the Oracle database can allocate more PGA memory than the parameter value of PGA_AGGREGATE_TARGET?“.
The answer is Yes. PGA_AGGREGATE_TARGET parameter in Oracle is not a hard limit. It is just a goal which means that the database attempts to keep the PGA memory allocations over all the sessions under this number but there can be some times when it exceeds that number. Things such as sort or hash areas fall under the portion of the PGA memory which Oracle can control the allocation size of. On the other hand, memory occupied by things like PL/SQL table variables is something that Oracle cannot control in the PGA memory. So, suppose that you have 1000 sessions connected to Oracle and if you have set PGA target to be say 1000MB. Suppose that each of these sessions is running code which is filling up a PL/SQL table variable with data which consumes say 2 MB of memory. Now, we have 2000MB of PGA memory allocated and there is nothing that Oracle can do to control that and that way, it will exceed the limit set by the PGA_AGGREGATE_TARGET parameter.