oracle18c修改sga大小,ORACLE 12C新特性-硬性限制PGA大小 | 信春哥，系统稳，闭眼上线不回滚！...

在12C版本之前，无法硬性限制PGA的大小，虽然可以设置PGA_AGGREGATE_TARGET参数，但ORACLE还是会根据实际需动态调整PGA的大小，在12C版本，新增PGA_AGGREGATE_LIMIT参数，限制PGA的最大值，类似于SGA_MAX_SIZE，该参数是动态参数，可以动态调整。

在10g和11g版本的ORACLE数据库中，只有一个PGA相关的非隐含参数。

–10g

[oracle@t10205 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on 121 04:07:49 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  show parameter pga

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

pga_aggregate_target                 big integer                       190M

–11g

[oracle@mining ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 11:24:02 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 600M

在12C版本中，新增PGA_AGGREGATE_LIMIT参数，来限制PGA的最大值。

[oracle@dbdream ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jan 18 19:18:57 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter pga

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

pga_aggregate_limit                  big integer            2G

pga_aggregate_target                 big integer            0

PGA_AGGREGATE_LIMIT参数设置非零值时，启用PGA的限制，在12cR1版本，默认开启对PGA的限制。如果该参数设置为零，则不对PGA进行限制。该参数可以动态调整。

SQL> alter system set pga_aggregate_limit=0;

System altered.