Increase PGA_AGGREGATE_LIMIT
Whenever you need more PGA to service clients or saw error ORA-04030, you may consider to raise PGA size online.
Let's see current settings.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 40G
pga_aggregate_target big integer 20G
I know you might consider to change PGA_AGGREGATE_TARGET, but the problem is that it's only an initial limit, it can be soon lifted whenever clients need more. That is, it's not the hard limit of PGA.
Set PGA_AGGREGATE_LIMIT
The real hard limit is PGA_AGGREGATE_LIMIT. Directly increasing PGA_AGGREGATE_LIMIT is a better way in such urgent situations. Even better, we can do it dynamically.
SQL> alter system set pga_aggregate_limit=50G scope=memory sid='*';
System altered.
In this case, we expanded it to 50GB online. For resource sufficient environments, you may set it to 0 which is unlimited. To persist the value, you may set SCOPE=BOTH.
Let's check the result.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 50G
pga_aggregate_target big integer 20G
Strictly speaking, PGA_AGGREGATE_LIMIT is not the hard limit, either. The hard limit is the physical memory of database server. So it's meaningless to overcommit PGA_AGGREGATE_LIMIT to a value where it can't reach.