Automatic Memory Management in Oracle 10g and 11g.

Automatic Memory Management in Oracle 11g.

Assuming our required memory requirement is 3G, we might issue the following statements.

CONN / AS SYSDBA


ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL SCOPE=SPFILE;


#Leave some room for possible future memory growth
#without instance restart.

ALTER SYSTEM SET MEMORY_MAX_TARGET=5G SCOPE=SPFILE;

-- Set the dynamic parameters. 
ALTER SYSTEM SET MEMORY_TARGET=3G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET DB_CACHE_SIZE=0 SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE=0 SCOPE=SPFILE;
ALTER SYSTEM SET LARGE_POOL_SIZE=0 SCOPE=SPFILE;
ALTER SYSTEM SET JAVA_POOL_SIZE=0 SCOPE=SPFILE;


-- Restart instance.
SHUTDOWN IMMEDIATE;
STARTUP;

Automatic Memory Management in Oracle 10g.


Assuming our required memory requirement is 3G, we might issue the following statements.

CONN / AS SYSDBA

ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL SCOPE=SPFILE;


#Leave some room for possible future memory growth
#without instance restart.

ALTER SYSTEM SET SGA_MAX_SIZE=5G SCOPE=SPFILE;

-- Set the dynamic parameters. Assuming Oracle has full control.
ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE;
ALTER SYSTEM SET DB_CACHE_SIZE=0 SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE=0 SCOPE=SPFILE;
ALTER SYSTEM SET LARGE_POOL_SIZE=0 SCOPE=SPFILE;
ALTER SYSTEM SET JAVA_POOL_SIZE=0 SCOPE=SPFILE;


-- Restart instance.
SHUTDOWN IMMEDIATE;
STARTUP;