By default, Oracle11g uses AMM (automatic memorymanagement, automatic memory management. During database installation, we specify that Oracle uses memory
By default, Oracle 11G uses AMM (Automatic Memory Management. During database installation, we specify that Oracle uses memory
By default, Oracle 11G uses AMM (Automatic Memory Management. During database installation, specify the percentage of memory used by Oracle. This value is used as the initial values of MEMORY_TARGET and MEMORY_MAX_TARGET. If the two parameters are set to a non-zero value, Oracle uses the AMM management policy.
The following shows how to modify the value of MEMORY_TARGET.
1. Test Environment
We operate in 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. View MEMORY_TARGET and related parameter values
We can use show parameter to view the initialization parameter values. Other related parameters include MEMORY_MAX_TARGET, sga_max_size, sga_target, and pga_aggregate_target.
SQL>
SQL> show parameter memory
NAME TYPE VALUE
-----------------------------------------------------------------------------
Hi_shared_memory_address integer 0
Memory_max_target big integer 500 M
Memory_target big integer 400 M
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 500 M
Sga_target big integer 0
SQL>
SQL> show parameter pga
NAME TYPE VALUE
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 0
SQL>
We can see that the MEMORY_MAX_TARGET of the database is 500 M, and the MEMORY_TARGET is 400 M.
3. Modify MEMORY_TARGET
We use the alter system statement to modify MEMORY_TARGET. Note that MEMORY_TARGET is a dynamic parameter while MEMORY_MAX_TARGET is a static parameter.
In this way, when we modify the value <= MEMORY_MAX_TARGET, we can modify the value of MEMORY_TARGET at will. However, when we modify the value> MEMORY_MAX_TARGET, we must first modify the value of the MEMORY_MAX_TARGET parameter, restart the database so that the static parameters take effect and then modify the MEMORY_TARGET value. Otherwise, an error is returned.
3.1 modify value <= MEMORY_MAX_TARGET
We try to change the value of the MEMORY_TARGET parameter from 500 M to M.
SQL>
SQL & gt; alter system set memory_target = 500 m;
System altered.
SQL>
SQL>
SQL> show parameter memory
NAME TYPE VALUE
-----------------------------------------------------------------------------
Hi_shared_memory_address integer 0
Memory_max_target big integer 500 M
Memory_target big integer 500 M
Shared_memory_address integer 0
SQL>
3.2 modify value> MEMORY_MAX_TARGET
We try to change the value of the MEMORY_TARGET parameter from 700 mb to MB. The database reports an error because the value of modification is> MEMORY_MAX_TARGET. We must first modify the value of the MEMORY_MAX_TARGET parameter and restart the database to make the static parameter take effect before modifying the value of MEMORY_TARGET.
SQL>
SQL & gt; alter system set memory_target = 600 m;
Alter system set memory_target = 600 m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified the value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>
SQL> alter system set memory_max_target = 800 m 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 700 M
Memory_target big integer 500 M
Shared_memory_address integer 0
SQL>
SQL & gt; alter system set memory_target = 600 m;
System altered.
SQL> show parameter memory
NAME TYPE VALUE
-----------------------------------------------------------------------------
Hi_shared_memory_address integer 0
Memory_max_target big integer 700 M
Memory_target big integer 600 M
Shared_memory_address integer 0
SQL>
SQL>
This article permanently updates the link address: