sga_max_size为实例允许使用的sga上限,一个静态参数,是不能动态修改的.
sga_target为10g推出的sga自动管理的参数,动态参数,可以动态修改.
当oracle初始化启动时,如果sga_max_size的值小于显式的指定的各sga组件值之和,则oracle将会忽略sga_max_size
以下为测试过程
SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 184M
db_cache_size big integer 100M
SQL> alter system set sga_max_size=96m scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 778816 bytes
Variable Size 95428032 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
当oracle初始化启动时,如果sga_max_size小于sga_target时,启动之后,sga_max_size将等于sga_target
以下为测试过程
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 280M
SQL> alter system set sga_max_size=120m scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 778816 bytes
Variable Size 95428032 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 280M
sga_target big integer 280M
SQL>
启动之后,sga_target就不能大于sga_max_size.以下为测试过程
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 280M
SQL> alter system set sga_target=320m scope=both;
alter system set sga_target=320m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
[@more@]
sga_target为10g推出的sga自动管理的参数,动态参数,可以动态修改.
当oracle初始化启动时,如果sga_max_size的值小于显式的指定的各sga组件值之和,则oracle将会忽略sga_max_size
以下为测试过程
SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 184M
db_cache_size big integer 100M
SQL> alter system set sga_max_size=96m scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 778816 bytes
Variable Size 95428032 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
当oracle初始化启动时,如果sga_max_size小于sga_target时,启动之后,sga_max_size将等于sga_target
以下为测试过程
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 280M
SQL> alter system set sga_max_size=120m scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 778816 bytes
Variable Size 95428032 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 280M
sga_target big integer 280M
SQL>
启动之后,sga_target就不能大于sga_max_size.以下为测试过程
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 280M
SQL> alter system set sga_target=320m scope=both;
alter system set sga_target=320m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9650775/viewspace-923231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9650775/viewspace-923231/