Init parameterMemory

MEMORY_TARGET

What it controls

Total memory for SGA and PGA together under Automatic Memory Management.

Know this before you change it

Can't be combined with Linux HugePages, so most Linux databases larger than a few GB leave it at 0 and set SGA_TARGET and PGA_AGGREGATE_TARGET instead. It can only be raised up to MEMORY_MAX_TARGET without a restart.

Default: 0

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'memory_target';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'memory_target' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET memory_target = 0 SCOPE = BOTH SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET memory_target SCOPE = SPFILE SID = '*';

Related scripts

• SGA components and PGA usageCurrent size of each SGA pool, then PGA target against what's actually allocated. PGA allocated well above target points at sort- or hash-heavy…

Open in the parameter referenceOracle's reference

More in Memory

• MEMORY_MAX_TARGETUpper limit for MEMORY_TARGET.
• SGA_TARGETSize of the SGA under Automatic Shared Memory Management. Oracle moves memory between the buffer cache, shared pool and other pools as load changes.
• SGA_MAX_SIZEThe most memory the SGA can grow to without a restart.
• PGA_AGGREGATE_TARGETTarget for the total private memory of all server processes, used for sorts, hash joins and bitmap operations.
• PGA_AGGREGATE_LIMITHard limit on total PGA (12c and later). Above it, Oracle ends the calls or sessions using the most PGA with ORA-04036.
• SHARED_POOL_SIZESize of the shared pool: parsed SQL, PL/SQL and the dictionary cache. Under SGA_TARGET it's a minimum.
• DB_CACHE_SIZESize of the default buffer cache. Under SGA_TARGET it's a minimum.
• USE_LARGE_PAGESWhether the SGA is placed in Linux HugePages.
• INMEMORY_SIZESize of the In-Memory column store. On 12.2 and later it can be increased while the instance is running.