下面演示一下如何修改MEMORY_TARGET的值。
1.测试环境
我们在Oracle 11g中进行操作。
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
2.查看MEMORY_TARGET和相关参数值
我们通过show parameter来查看初始化参数值，其他相关的参数包括MEMORY_MAX_TARGET、sga_max_size、sga_target、pga_aggregate_target等。
SQL>
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 500M
memory_target big integer 400M
shared_memory_address integer 0
SQL>
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 0
SQL>
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL>
我们看到，数据库的MEMORY_MAX_TARGET为500M，MEMORY_TARGET为400M。
3.修改MEMORY_TARGET
? ? 我们通过alter system语句来修改MEMORY_TARGET，需要注意的是，MEMORY_TARGET是个动态参数，而MEMORY_MAX_TARGET是个静态参数。
? ? 这样一来，当修改值<=MEMORY_MAX_TARGET时，我们可以随意修改MEMORY_TARGET值；但是，当修改值>MEMORY_MAX_TARGET时，我们必须首先修改MEMORY_MAX_TARGET参数值，重启数据库使静态参数生效后再去修改MEMORY_TARGET值，否则，数据库会报错。
3.1修改值<=MEMORY_MAX_TARGET
我们尝试将MEMORY_TARGET参数值由400M修改为500M。
SQL>
SQL> alter system set memory_target=500m;
System altered.
SQL>
SQL>
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 500M
memory_target big integer 500M
shared_memory_address integer 0
SQL>
3.2修改值>MEMORY_MAX_TARGET
? ?
我们尝试将MEMORY_TARGET参数值由500M修改为700M。由于修改值>MEMORY_MAX_TARGET,数据库会报错;我们必须首先修改MEMORY_MAX_TARGET参数值，重启数据库使静态参数生效后再去修改MEMORY_TARGET值。
SQL>
SQL> alter system set memory_target=600m;
alter system set memory_target=600m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>
SQL> alter system set memory_max_target=800m scope=spfile;
System altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 732352512 bytes
Fixed Size 1347456 bytes
Variable Size 603979904 bytes
Database Buffers 121634816 bytes
Redo Buffers 5390336 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 700M
memory_target big integer 500M
shared_memory_address integer 0
SQL>
SQL> alter system set memory_target=600m;
System altered.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 700M
memory_target big integer 600M
shared_memory_address integer 0
SQL>
SQL>