#Oracle memory settings how to#

#Oracle memory settings how to#

In below steps, we will see how to set minimum values for these parameters even when you choose to implement automatic memory management. You donвЂ™t have to set the SGA_TARGET and PGA_AGGREGATE_TARGET parameters to 0 in order to use automatic memory management. It may exceed the target allocation on occasion, if necessary. Note: The term вЂњtargetвЂќ in parameters such as memory_target and pga_memory_target means just that Oracle will try to stay under the target level, but thereвЂ™s no guarantee that itвЂ™ll never go beyond that. You can change the value of the MEMORY_TARGET parameter dynamically anytime, as long as you donвЂ™t exceed the value you set for the MEMORY_MAX_TARGET parameter. Turn on automatic memory management by setting the MEMORY_TARGET parameter: SQL> alter system set memory_target = 1000M ДЇrom this point on, the database runs under the automatic memory management mode, with it shrinking and growing the individual allocations to the various components of Oracle memory according to the requirements of the ongoing workload. SQL> alter system set pga_aggregate_target = 0 Д¦. Turn off the SGA_TARGET and the PGA_AGGREGATE_TARGET parameters by issuing the following ALTER SYSTEM commands: SQL> alter system set sga_target = 0 Note that if youвЂ™ve started the instance with a traditional init.ora parameter file instead of the SPFILE, you must add the following to your init.ora file: memory_max_target = 2000MДҐ. You must specify the SCOPE parameter in the alter system command, because MEMORY_MAX_TARGET isnвЂ™t a dynamic parameter, which means you canвЂ™t change it on the fly while the instance is running.ДЈ.

Assuming youвЂ™re using the SPFILE, first set a value for the MEMORY_MAX_TARGET parameter: SQL> alter system set memory_max_target=2G scope=spfile Connect to the database with the SYSDBA privilege.Дў. We assume that we are going to allocate 2,000 MB to the MEMORY_MAX_TARGET parameter and 1,000 MB to the MEMORY_TARGET parameter.ДЎ. Here are the steps to implement automatic memory management in your database, if youвЂ™ve already set either the SGA_TARGET or the PGA_AGGREGATE_TARGET parameters (or both).

If you want to automate memory management in your Oracle database, you can take advantage of the automatic memory management feature built into Oracle Database 11g.