SETTING VALUES FOR PARAMETERS SGA_MAX_SIZE, MEMORY_TARGET AND MEMORY_MAX_TARGET :

SGA_MAX_SIZE is dependent on MEMORY_MAX_TARGET and cannot be greater than it. So first increase the MEMORY_MAX_TARGET and then sga_max_size.

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
--------------------------------

select * from v$sga_target_advice order by sga_size;

--------------------------------

Run the following query to obtain a value for SGA_TARGET:

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

---------------------------

Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:

---------------------------

ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]

---------------------------

---SGA_TARGET should never be greater than Maximum SGA Size. It can be equal to but not greater than.

---------------------------

You can take advantage of automatic shared memory management by setting Total SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following statements:

ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
where 992M = 1200M minus 208M.


SELECT SUM(value) FROM V$SGA
SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY

------------------------------

-- Increasing the Maximum SGA Size and greater than SGA_TARGET :

show sga;

create pfile from spfile;

alter system set sga_max_size=20m scope=spfile;

shut immediate;

sqlplus /nolog

connect 'sys/sys@orclnew' as sysdba

Enter password:(Just Hit Enter.Donot provide password)
Connected to an idle instance.

startup mount

alter database open;


-----------------------------


To check SGA currenly allocated I would use:

select sum(value) from v$sga;

or

select sga_size from v$sga_target_advice where sga_size_factor=1;


To check PGA currently allocated I would use:

select value from v$pgastat where name like 'total%alloc%';


------------------------------

COLUMN name FORMAT A30
COLUMN value FORMAT A10
SELECT name, value
FROM   v$parameter
WHERE  name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM   v$pgastat
WHERE  name = 'maximum PGA allocated';



-- Calculate MEMORY_TARGET

SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,(SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;