12.1.0.2与12.2.0.1 sga大小调整记录

12.1.0.2与12.2.0.1 sga大小调整记录

12.1.0.2

cdb

SQL> conn / as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1564M
sga_target                           big integer 5G
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 5G
sga_target                           big integer 5G
unified_audit_sga_queue_size         integer     1048576

pdb
SQL> show con_name

CON_NAME
------------------------------
TY

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1564M
sga_target                           big integer 5G

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 5G
sga_target                           big integer 5G
unified_audit_sga_queue_size         integer     1048576


SQL> alter system set sga_max_size=4g scope=spfile;
alter system set sga_max_size=4g scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL> alter system set sga_target=4g scope=spfile;
alter system set sga_target=4g scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

12.2.0.1
cdb

SQL> conn / as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1G
sga_target                           big integer 3G
target_pdbs                          integer     6

pdb

SQL> show con_name

CON_NAME
------------------------------
PDBORCL
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 300M
sga_target                           big integer 3G
target_pdbs                          integer     6

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 3G
sga_min_size                         big integer 0
sga_target                           big integer 3G
unified_audit_sga_queue_size         integer     1048576



SQL> alter system set sga_target=2g scope=both;

System altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 3G
sga_min_size                         big integer 0
sga_target                           big integer 2G
unified_audit_sga_queue_size         integer     1048576


SQL> alter system set sga_target=4g scope=both;
alter system set sga_target=4g scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56747: invalid value 4294967296 for parameter sga_target; must be smaller
than parameter sga_target of the root container

测试结果：12.2.0.1可以对每个pdb调整最大的sga大小，12.1.0.2不行

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/using-oracle-resource-manager-for-pdbs-with-sql-plus.html#GUID-A3459A8B-A36A-44D4-9FCD-75CA0E3D3252