Check the instance Memory Parameter include SGA, PGA size

Check Total SGA plus PGA Size

select sum(bytes)/1024/1024 as "Total PGA+SGA Mbytes" from
(select value as bytes from v$sga union all
select value as bytes from v$sesstat s,
v$statname n where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory');


Find out the instance memory parameters

col name for a30
col VALUE for a30
select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size',
'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target',
'workarea_size_policy', 'streams_pool_size' ) ;


Check parameters at Session and Instance level

col Parameter for a30
col "Session Value" for a25
col "Instance Value" for a25
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size',
'__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');


Find out the PGA allocated memory in MB for particulars SIDs

COLUMN USED_MEMORY FORMAT 99999D9
COLUMN ALLOC_MEMORY FORMAT 99999D9
COLUMN FREE_MEMEOY FORMAT 99999D9
COLUMN MAX_MEMORY FORMAT 99999D9
COLUMN username FORMAT a12
COLUMN program FORMAT a20
COLUMN sid FORMAT a5
COLUMN spid FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
SUBSTR(s.program,1,22) program , s.process pid_remote,
s.status,
ROUND(pga_used_mem/1024/1024) "USED_MEMORY",
ROUND(pga_alloc_mem/1024/1024) "ALLOC_MEMORY",
ROUND(pga_freeable_mem/1024/1024) "FREE_MEMEOY",
ROUND(pga_max_mem/1024/1024) "MAX_MEMORY"
FROM v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem,logon_time;