Oracle memory leak info

How to check if oracle instance is leaking memory

Oracle memory leak info


col name format a20
select sid, substr(name,1,30) name , value , value/(1024*1024) "MB"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and name like '%a memory'
order by sid, name;

set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10

select b.sid, substr(name,1,30) name, value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,c.osuser,c.machine,
-- c.terminal,
c.program,c.module,state,logon_time
from v$statname a, v$sesstat b,v$session c
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=c.sid
and osuser!='oracle'
order by status,MB desc,sid, name;




set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10

select b.sid, substr(name,1,30), value , value/(1024*1024) "MB",s.sid ssid, s.serial#,
-- ,s.sid ssid,
s.status,s.username,s.schemaname,s.osuser,s.machine,
-- s.terminal,
--s.program,
s.module,state,logon_time,substr(p.spid,1,8) spid,
--substr(sa.sql_text,1,2000) txt
sa.sql_id
from v$statname a, v$sesstat b,v$session s, v$process p ,v$sqlarea sa
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=s.sid
and osuser!='oracle'
and p.addr = s.paddr
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
order by status,MB desc,sid, name;


set long 4000
select sql_text from v$sqlarea where sql_id='&1'