Oracle 11g修改MEMORY_TARGET

默认情况下，Oracle 11g是使用AMM（Automatic Memory Management，自动内存管理）的。我们在安装数据库过程中，指定Oracle使用内存的百分比，这个取值就作为MEMORY_TARGET和MEMORY_MAX_TARGET的初始取值使用。如果这两个参数设置为非零取值，那么Oracle就是采用AMM管理策略的。下面演示一下如何修改MEMORY_TARGET的值。1.测试环境我们在Oracle 11g中进行操作。SQL>SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL>2.查看MEMORY_TARGET和相关参数值我们通过show parameter来查看初始化参数值，其他相关的参数包括MEMORY_MAX_TARGET、sga_max_size、sga_target、pga_aggregate_target等。SQL>SQL> show parameter memoryNAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0memory_max_target big integer 500Mmemory_target big integer 400Mshared_memory_address integer 0SQL>SQL> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 500Msga_target big integer 0SQL>SQL> show parameter pgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target big integer 0SQL>我们看到，数据库的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 memoryNAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0memory_max_target big integer 500Mmemory_target big integer 500Mshared_memory_address integer 0SQL>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 invalidORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGETSQL>SQL> alter system set memory_max_target=800m scope=spfile;System altered.SQL> shu immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>SQL> startupORACLE instance started.Total System Global Area 732352512 bytesFixed Size 1347456 bytesVariable Size 603979904 bytesDatabase Buffers 121634816 bytesRedo Buffers 5390336 bytesDatabase mounted.Database opened.SQL>SQL> show parameter memoryNAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0memory_max_target big integer 700Mmemory_target big integer 500Mshared_memory_address integer 0SQL>SQL> alter system set memory_target=600m;System altered.SQL> show parameter memoryNAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0memory_max_target big integer 700Mmemory_target big integer 600Mshared_memory_address integer 0SQL>SQL>更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址