sga_target是动态参数,可以调整后马上生效的
但是前提是当前分配的sga_max_size下必须能够承载分配的内存量
这个参数的值只有0和非零两种选择,非零时时和sga_max_size大小保持一致的
所以如果要吧sga_target改成大于sga_max_size的值就必须按照静态参数的改法,重新启动实例
重启后默认的sga_max_size会自动调整到和sga_target一样大小的
---------------------
版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
使用spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string F:\ORACLE\PRODUCT\10.1.0\DB_1\
调整前
SQL> set wrap off
SQL> set linesize 159
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 0
调整为小于sga_max_size值
SQL> alter system set sga_target=200m;
System altered.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 300M
调整为大于sga_max_size值
SQL> alter system set sga_target=500m;
alter system set sga_target=500m
*
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
按照静态参数的方式调整
SQL> alter system set sga_target=500m scope=spfile;
System altered.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 300M
重启验证
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 789672 bytes
Variable Size 145486680 bytes
Database Buffers 377487360 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 500M
sga_target big integer 500M
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13177610/viewspace-671812/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13177610/viewspace-671812/