有幸遇到这个错误了, 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.


limit of aggregate PGA memory consumed by the instance


  1. 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> /

    —————————————————————- ———- ————
    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.




沪公网安备 31010802001379号