1
说明
在12.2中,可以通过PDB的初始化参数来限制PDB的SGA和PDB的大小。
参数为:
l DB_CACHE_SIZE
2 SHARED_POOL_SIZE
3 PGA_AGGREGATE_LIMIT
4 PGA_AGGREGATE_TARGET
5 SGA_MIN_SIZE
6 SGA_TARGET
关于参数的详细说明,参考官方文档:https://www.cndba.cn/Expect-le/article/2500
https://www.cndba.cn/Expect-le/article/2500
要想使用以上参数来控制PDB的内存使用,必须要满足下面两种情况:
https://www.cndba.cn/Expect-le/article/2500
1.
NONCDB_COMPATIBLE参数必须设置为false。
2.
CDB中的MEMORY_TARGET设置为0或不设置。
2
具体例子
2.1
查看参数是否满足
SQL> show parameter NONCDB_COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------------------
noncdb_compatibleboolean FALSE
SQL> show parameter memory_target
NAME TYPE VALUE
------------------- ---------------- ----------------------
memory_targetbig integer 1392M
SQL> alter system set memory_target=0 scope=spfile;
System altered.
重启数据库。
SQL> show parameter memory_target
NAME TYPE VALUE
------------------- ---------------- ----------------------
memory_targetbig integer 0
2.2
设置PDB的PGA大小
切换到PDB下执行。
https://www.cndba.cn/Expect-le/article/2500
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 LEIPDB MOUNTED -要打开
SQL> alter session set container=leipdb;
Session altered.
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 90M SCOPE = BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 90M SCOPE = BOTH
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter database open;
Database altered.
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 90M SCOPE = BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 90M SCOPE = BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093:pga_aggregate_limit must be between 256M and 100000G --提示必须要大于等于256M
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT =256M SCOPE = BOTH;
System altered.
https://www.cndba.cn/Expect-le/article/2500
不需要重启。
查看结果
SQL> show parameter PGA_AGGREGATE_LIMIT
NAME TYPE VALUE
----------------------------------- ----------------------
pga_aggregate_limitbig integer 256M
其他参数修改也一样。
注意:https://www.cndba.cn/Expect-le/article/2500
l
所有的参数大小都不要超过CDB的相应参数的总大小。https://www.cndba.cn/Expect-le/article/2500
l
所有参数的修改,必须是在相应的PDB下执行。
否则,如下报错:
https://www.cndba.cn/Expect-le/article/2500
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 257G SCOPE = BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 257G SCOPE = BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56747: invalid value 275951648768 for parameter pga_aggregate_limit; must
be smaller than parameter pga_aggregate_limit of the root container
版权声明:本文为博主原创文章,未经博主允许不得转载。
PGA 内存