有幸遇到这个错误了， 12c的PGA_AGGREGATE_LIMIT 限制PGA功能还是有用的：
Exception in thread “main” java.sql.SQLException: ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
oracle@localhost:/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace$ oerr ora 4036
04036, 00000, “PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT”
// *Cause: Private memory across the instance exceeded the limit specified
// in the PGA_AGGREGATE_LIMIT initialization parameter. The largest
// sessions using Program Global Area (PGA) memory were interrupted
// to get under the limit.
// *Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce
// memory usage.
pga_aggregate_limit
limit of aggregate PGA memory consumed by the instance
If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most PGA memory.
Abortion and termination of sessions and processes are based on the following conditions:
■Sessions and processes consuming the most PGA memory are aborted.
■If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then sessions and processes consuming the most PGA memory are terminated.
■Parallel queries are treated as a single unit.
■User sessions in consumer groups are treated based on the percentage of PGA memory specified for the consumer group in the resource plan.
■SYS and other critical background processes are not subject to terminations.
The V$PGASTAT view contains statistics about the number of sessions that are aborted or terminated due to excessive PGA memory usage since the database was started.
1* select name,value,unit from v$pgastat
SQL> /
NAME VALUE UNIT
—————————————————————- ———- ————
aggregate PGA target parameter 218103808 bytes
aggregate PGA auto target 77856768 bytes
global memory bound 43620352 bytes
total PGA inuse 131596288 bytes
total PGA allocated 163753984 bytes
maximum PGA allocated 1423985664 bytes
total freeable PGA memory 20447232 bytes
process count 39
max processes count 43
PGA memory freed back to OS 2360541184 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 2054144 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 163
bytes processed 414296064 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 7889
19 rows selected.