Oracle11g modify MEMORY_TARGET

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: