Tuesday, July 28, 2009

Open Cursor Per session

declare cursor c1 is
select distinct(s.sid) sid,s.serial# serial, a.value cumulative, c.value current_open
from v$sesstat a, v$statname b,v$sesstat c, v$statname d,v$session s
where a.statistic# = b.statistic#
and c.statistic# = d.statistic#
and s.sid=a.sid
and s.sid=c.sid
and b.name = 'opened cursors cumulative'--current'
and d.name = 'opened cursors current'
and s.machine like '%machine name%'
and s.program like 'JDBC%'
and s.module like 'JDBC%'
order by s.sid;
begin
for eachrow in c1 loop
dbms_output.put_line(to_char(systimestamp,'DDMMYYYYhh24mi')||' '||eachrow.sid||' '||eachrow.serial||eachrow.cumulative||' '||eachrow.current_open);
end loop;
end ;

No comments: