Reduce Memory Usage from 16GB to 8GB - Oracle

Question

I had created a oracle instance using "Database Configuration Assistant". My system is having 64GB RAM. I had given 16GB to oracle instance, in Initialization Parameters Wizard.

Now i want to reduce that 16GB to 8GB. So that, the RAM occupied by oracle will be 8GB. I had tried this in SQL Developer,

ALTER SYSTEM SET pga_aggregate_target = 8289 M;
ALTER SYSTEM SET sga_target = 1536 M;

I had restarted the oracle service. It not got reflected. Still the oracle is using 16GB.

I dont know whether this is correct. Whether system reboot is needed for this.? or else how to reduce the memory usage.

Answer 1

There are various ways to define the amount of memory used. Historically, you needed a lot of settings to change to impact total memory footprint. Nowadays, it is often by default setting only one and start tweaking later (when the Oracle installer does not screw up; it often sets things wrongly).

I would check the following:

select *
from   v$parameter
where  name like '%size%' 
       or 
       name like '%target%'

Check which ones have been set and need changing. It can be settingslike shared_pool_size, memory_target, sga_target, and others.

When you change it, some settings (depends on version and edition) can be changed while the instance is open and running, while some require a restart. Also, sometimes you are using a text file (pfile) and in some instance you may be using a binary file (spfile). Binary file is pre-condition to allow online changing without restarting.

You will probably succeed using something like:

alter system set NAME = VALUE scope=[spfile|both]

as sys user. Scope=spfile only changes the spfile, both changes runtime and spfile. When using a pfile like init*.ora, you just edit the text file and restart your instance.

To quickly restart, the best way is IMHO:

startup force

Please decreasing size, you will generally not have a problem assuming that the size is sufficient to handle the load. Do it in a test environment first. When increasing and depending on platform, please make sure first that your new settings can be handled. For instance, increasing memory allocated on Linux may require you to change kernel settings. Otherwise, your Oracle instance will not start unless the corrections are made first.