Set up a friendly environment to share my understanding and ideas about Oracle / Oracle Spatial database administration, ESRI ArcSDE Geodatabase administration and UNIX (Solaris) operating system.
MAX_DUMP_FILE_SIZE = {integer[K | M | G] | UNLIMITED }
Default value
UNLIMITED
Modifiable
ALTER SESSION, ALTER SYSTEM
Range of values
0 to unlimited, or UNLIMITED
Basic
No
MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.
·A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
·A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.
·The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.
1. The MAX_ENABLED_ROLES parameter is deprecated. It is retained for backward compatibility only.
MAX_ENABLED_ROLES specifies the maximum number of database roles that users can enable, including roles contained within other roles. The actual number of roles that users can enable is 2 plus the value of MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC and the user's own role.
For example, if MAX_ENABLED_ROLES is set to 5, then user HR can have seven roles enabled (the five enabled by MAX_ENABLED_ROLES, plus PUBLIC and HR).
If MAX_SHARED_SERVERS is specified, then it should be greater than or equal to SHARED_SERVERS and less than PROCESSES.
Basic
No
MAX_SHARED_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.
When you want to reduce the range of shared servers, you can reduce MAX_SHARED_SERVERS before reducing SHARED_SERVERS. If MAX_SHARED_SERVERS is lower than SHARED_SERVERS, then the number of shared servers will not vary but will remain at the constant level specified by SHARED_SERVERS. If MAX_SHARED_SERVERS is not specified, then a shared server process may be spawned as long as the number of free process slots is greater than 1 / 8 the maximum number of processes, or 2 if PROCESSES is less than 24.
0 to the physical memory size available to the Oracle Database
Basic
No
MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter. See the description of MEMORY_TARGET for more information about how the settings of MEMORY_MAX_TARGET and MEMORY_TARGET affect each other.
Note:
1. This parameter was introduced in 11g.
2. 11g MEMORY_TARGET Parameter Dependency
If MEMORY_TARGET is set to a non-zero value:
If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively. MEMORY_TARGET can take values from SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_SIZE.
If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET).
If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)) and will auto-tune subcomps.
If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total server memory in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% for sga and 40% for PGA at startup.
If MEMORY_TARGET is not set or set to set to 0 explicitly (default value is 0 for 11g):
If SGA_TARGET is set we will only auto-tune the sizes of the sub-components of the SGA. PGA will be autotuned independent of whether it is explicitly set or not. Though the whole SGA(SGA_TARGET) and the PGA(PGA_AGGREGATE_TARGET) will not be auto-tuned, i.e., will not grow or shrink automatically.
If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow the same policy as we have today; PGA will be auto-tuned and the SGA will not be auto-tuned and parameters for some of the sub-components will have to be set explicitly (for SGA_TARGET).
If only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and we will not auto tune sga and pga. It will default to 10gR2 behavior within sga and pga.
If sga_max_size is not user set, we will internally set it to MEMORY_MAX_TARGET.
In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
If you wish to monitor the decisions made by Automatic Memory Management following views can be useful
V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memory components
V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 SGA resize requests
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.
In a text-based initialization parameter file, if you omit MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
Note:
1. This parameter was introduced in 11g.
2. 11g MEMORY_TARGET Parameter Dependency
If MEMORY_TARGET is set to a non-zero value:
If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively. MEMORY_TARGET can take values from SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_SIZE.
If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET).
If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)) and will auto-tune subcomps.
If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total server memory in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% for sga and 40% for PGA at startup.
If MEMORY_TARGET is not set or set to set to 0 explicitly (default value is 0 for 11g):
If SGA_TARGET is set we will only auto-tune the sizes of the sub-components of the SGA. PGA will be autotuned independent of whether it is explicitly set or not. Though the whole SGA(SGA_TARGET) and the PGA(PGA_AGGREGATE_TARGET) will not be auto-tuned, i.e., will not grow or shrink automatically.
If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow the same policy as we have today; PGA will be auto-tuned and the SGA will not be auto-tuned and parameters for some of the sub-components will have to be set explicitly (for SGA_TARGET).
If only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and we will not auto tune sga and pga. It will default to 10gR2 behavior within sga and pga.
If sga_max_size is not user set, we will internally set it to MEMORY_MAX_TARGET.
In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
If you wish to monitor the decisions made by Automatic Memory Management following views can be useful
V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memory components
V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 SGA resize requests
I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this BLOG are provided "as is" with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.
