Change SGA / PGA targets

Command

-- Automatic memory management (ASMM) targets:
ALTER SYSTEM SET sga_target=8G   SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=BOTH;

-- Upper limit (static, requires restart):
ALTER SYSTEM SET sga_max_size=12G SCOPE=SPFILE;

-- View the current memory distribution:
SELECT component, current_size/1024/1024 AS mb
FROM   v$sga_dynamic_components ORDER BY component;

What it does

Sets the SGA and PGA memory targets; Oracle manages the components automatically (ASMM/AMM).

When to use it

When RAM is added to the server, during a memory bottleneck or memory pressure.

Caution

sga_target cannot exceed sga_max_size. sga_max_size is static and requires a restart. Leave RAM for the operating system and other instances; over-allocation causes swapping/OOM.