SGA_TARGET specifies the total size of all SGA components.
Modifiable in a PDB
Range of values
64 MB to operating system-dependent
SGA_TARGET is specified, then the following memory pools are automatically sized:
Buffer cache (
Shared pool (
Large pool (
Java pool (
Streams pool (
Data transfer cache (
If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
Other buffer caches, such as
RECYCLE, and other block sizes
Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for
SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
In the Default value field,
IMMEDIATE mode autotuning requests are necessary to avoid
ORA-04031 errors. The
IMMEDIATE modes are reflected in the
OPER_MODE column of the
If Automatic Memory Management is enabled (
MEMORY_TARGET is set to a positive value) and
SGA_TARGET is also set to a positive value, the
SGA_TARGET value acts as the minimum value for the size of the SGA.
This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it specifies the maximum SGA that the PDB can use at any time. When this parameter is not set at the PDB level, the PDB has no limit for the amount of SGA it can use, other than the CDB's SGA size.
To be able to use Resource Manager in a CDB to control the amount of memory each PDB can use:
NONCDB_COMPATIBLE initialization parameter must be set to
FALSE at the CDB level (in the root of the CDB).
MEMORY_TARGET initialization parameter must not be set at the CDB level.
You must set the
SGA_TARGET initialization parameter at the CDB level.
You must set
SGA_TARGET in a PDB to a value that is less than or equal to the
SGA_TARGET value set at the CDB level.
If you set a PDB’s
SGA_TARGET value and
SGA_TARGET is not set at the CDB level, you will not receive an error message and the PDB’s
SGA_TARGET value will not be enforced.
If you set
SGA_TARGET in a PDB to a value that is greater than the
SGA_TARGET value at the CDB level, you receive an error. If this requirement is violated after the PDB’s parameter is set (for example, if the
SGA_TARGET value is changed at the CDB level, Oracle will adjust the PDB’s value to meet this requirement
Oracle Database Administrator’s Guide for more information about the initialization parameters that control the memory usage of PDBs
Oracle Database Administrator’s Guide for information on automatic memory management
Oracle Database Administrator’s Guide for information on managing the SGA manually