MEMORY_MAX_TARGET - Set Maximum Memory for Oracle AMM

MEMORY_MAX_TARGET

Overview

MEMORY_MAX_TARGET sets the absolute upper limit that MEMORY_TARGET can ever reach, either at startup or when adjusted dynamically at runtime. It acts as a ceiling for Oracle’s Automatic Memory Management (AMM) system, which controls the combined SGA and PGA automatically. If MEMORY_MAX_TARGET is 0 (the default), Oracle derives it from MEMORY_TARGET at instance startup and AMM is bounded by that initial value.

Setting MEMORY_MAX_TARGET higher than the initial MEMORY_TARGET gives the DBA the flexibility to grow MEMORY_TARGET later with a simple ALTER SYSTEM command — without bouncing the instance. This is the primary reason to set it explicitly.

Parameter Type: Static (requires instance restart to change) Default Value: 0 (Oracle sets it equal to MEMORY_TARGET at startup) Valid Range: 0 to OS-dependent maximum physical memory Available Since: Oracle Database 11g Release 1 (11.1) Modifiable: No — must be set in SPFILE and requires restart PDB Modifiable: No — CDB-level only

Configuration

Viewing Current Value

-- Check both MEMORY_MAX_TARGET and MEMORY_TARGET in one query
SELECT name,
       value,
       display_value,
       description
FROM   v$parameter
WHERE  name IN ('memory_max_target', 'memory_target')
ORDER BY name;

-- Check what is stored in SPFILE (persistent value)
SELECT name,
       value,
       display_value
FROM   v$spparameter
WHERE  name IN ('memory_max_target', 'memory_target');

-- Confirm AMM is active (MEMORY_TARGET > 0 means AMM is enabled)
SELECT name,
       value / (1024 * 1024) AS value_mb
FROM   v$parameter
WHERE  name IN ('memory_target', 'memory_max_target', 'sga_target', 'pga_aggregate_target')
ORDER BY name;

Setting the Parameter

MEMORY_MAX_TARGET is static and can only be changed via SPFILE. You must restart the instance for the new value to take effect.

-- Set MEMORY_MAX_TARGET to 8 GB, leaving MEMORY_TARGET at its current value
-- This reserves headroom to grow MEMORY_TARGET later without a restart
ALTER SYSTEM SET memory_max_target = 8G SCOPE = SPFILE;

-- Typical setup: set ceiling 25-50% above initial target
-- e.g., MEMORY_TARGET = 6G, MEMORY_MAX_TARGET = 8G
ALTER SYSTEM SET memory_target     = 6G SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target = 8G SCOPE = SPFILE;

-- After restarting, you can grow MEMORY_TARGET dynamically up to 8G
ALTER SYSTEM SET memory_target = 7G;   -- no restart needed
ALTER SYSTEM SET memory_target = 8G;   -- still no restart needed (at ceiling)

-- Verify after restart
SELECT name, display_value FROM v$parameter
WHERE  name IN ('memory_max_target', 'memory_target');

Tuning Guidance

Recommended Values

Environment	MEMORY_TARGET	MEMORY_MAX_TARGET	Notes
Small OLTP (< 8 GB RAM)	2–4 GB	MEMORY_TARGET + 1 GB	Tight headroom on small servers
Medium OLTP (16–32 GB RAM)	8–12 GB	MEMORY_TARGET + 25%	Comfortable growth room
Large OLTP (64+ GB RAM)	20–40 GB	MEMORY_TARGET + 50%	Allow significant dynamic adjustment
Data Warehouse	30–60 GB	MEMORY_TARGET + 25%	Large PGA requirements
RAC Node	Per-node SGA + PGA	Per-node ceiling	Set per instance

How to Size

The key constraint on Linux is /dev/shm. Oracle AMM maps shared memory through the /dev/shm tmpfs filesystem. MEMORY_MAX_TARGET must not exceed the size of /dev/shm.

-- Step 1: Check current SGA and PGA consumption to establish a baseline
SELECT 'SGA'  AS component,
       sum(value) / (1024 * 1024) AS current_mb
FROM   v$sga
UNION ALL
SELECT 'PGA Target',
       value / (1024 * 1024)
FROM   v$parameter
WHERE  name = 'pga_aggregate_target'
UNION ALL
SELECT 'PGA Actual',
       value / (1024 * 1024)
FROM   v$pgastat
WHERE  name = 'total PGA allocated';

-- Step 2: Check historical peak PGA usage to size PGA headroom
SELECT value / (1024 * 1024) AS peak_pga_mb
FROM   v$pgastat
WHERE  name = 'maximum PGA allocated';

-- Step 3: Review AMM auto-tuned component sizes over time
SELECT component,
       current_size  / (1024 * 1024) AS current_mb,
       min_size      / (1024 * 1024) AS min_mb,
       max_size      / (1024 * 1024) AS max_mb,
       user_specified_size / (1024 * 1024) AS user_specified_mb,
       last_oper_type,
       last_oper_mode
FROM   v$memory_dynamic_components
ORDER BY current_size DESC;

# Check /dev/shm size on Linux — MEMORY_MAX_TARGET must not exceed this
df -h /dev/shm

# If /dev/shm is too small, resize it in /etc/fstab:
# tmpfs /dev/shm tmpfs defaults,size=12g 0 0
# Then: mount -o remount /dev/shm

Monitoring

-- Monitor current AMM memory distribution
SELECT component,
       current_size / (1024 * 1024)  AS current_mb,
       max_size     / (1024 * 1024)  AS max_mb
FROM   v$memory_dynamic_components
WHERE  current_size > 0
ORDER BY current_size DESC;

-- Check if MEMORY_TARGET is near the MEMORY_MAX_TARGET ceiling
SELECT mt.value  / (1024 * 1024) AS memory_target_mb,
       mmt.value / (1024 * 1024) AS memory_max_target_mb,
       ROUND(mt.value / mmt.value * 100, 1) AS pct_of_ceiling
FROM   v$parameter mt, v$parameter mmt
WHERE  mt.name  = 'memory_target'
AND    mmt.name = 'memory_max_target';

If pct_of_ceiling is above 90%, consider raising MEMORY_MAX_TARGET (requires restart) before the next planned maintenance window, then grow MEMORY_TARGET dynamically as needed.

Common Issues

ORA-00845: MEMORY_TARGET not supported on this system

This error occurs at startup when /dev/shm is smaller than MEMORY_TARGET (or MEMORY_MAX_TARGET). Oracle cannot allocate the required shared memory.

Resolution:

# Verify /dev/shm size
df -h /dev/shm

# Option 1: Increase /dev/shm (persistent, recommended)
# Edit /etc/fstab — change or add:
# tmpfs /dev/shm tmpfs defaults,size=8g 0 0
mount -o remount /dev/shm

# Option 2: Reduce MEMORY_MAX_TARGET to fit within /dev/shm
# Connect with STARTUP NOMOUNT and edit spfile:
# ALTER SYSTEM SET memory_max_target = 4G SCOPE = SPFILE;

See the full ORA-00845 guide for step-by-step diagnosis.

MEMORY_MAX_TARGET cannot be set smaller than MEMORY_TARGET

If you try to set MEMORY_MAX_TARGET below the current MEMORY_TARGET value, Oracle rejects the change.

-- Correct sequence: lower MEMORY_TARGET first, then MEMORY_MAX_TARGET
ALTER SYSTEM SET memory_target     = 4G SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target = 5G SCOPE = SPFILE;
-- Restart required for both changes to take effect cleanly

AMM conflicts with hugepages on Linux

Linux hugepages (large pages) cannot be used when AMM (MEMORY_TARGET) is enabled because AMM requires /dev/shm memory-mapped files, which are incompatible with hugepages.

Resolution: Choose one approach:

Use AMM: Disable hugepages. Set MEMORY_TARGET and MEMORY_MAX_TARGET. Simpler management.
Use hugepages: Disable AMM (MEMORY_TARGET = 0). Use SGA_TARGET + PGA_AGGREGATE_TARGET instead. Better performance for large SGAs (> 8 GB).

-- Switch from AMM to ASMM (to use hugepages)
ALTER SYSTEM SET memory_target        = 0   SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target    = 0   SCOPE = SPFILE;
ALTER SYSTEM SET sga_target           = 8G  SCOPE = SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 2G  SCOPE = SPFILE;
-- Restart required

Parameter	Relationship
`MEMORY_TARGET`	The active AMM memory ceiling; cannot exceed `MEMORY_MAX_TARGET`
`SGA_TARGET`	Used instead of AMM when `MEMORY_TARGET = 0`; ignored when AMM is active
`SGA_MAX_SIZE`	Static SGA ceiling used with ASMM; irrelevant when AMM is active
`PGA_AGGREGATE_TARGET`	PGA floor hint when AMM is active; ignored as a hard limit
`PGA_AGGREGATE_LIMIT`	Hard PGA cap (12c+); applies regardless of AMM

Version Notes

Version	Notes
11.1	`MEMORY_TARGET` and `MEMORY_MAX_TARGET` introduced with AMM
11.2+	Behavior unchanged; hugepages incompatibility documented
12c+	CDB/PDB architecture; both parameters are CDB-level only
19c+	AMM still supported; Oracle recommends ASMM + hugepages for large instances
21c+	No functional change; AMM remains available but ASMM is preferred at scale