Check open cursor in oracle user wise and session wise.

Open cursor always slow database speed. So developer take care to every time when open cursor after computation need to close cursor
But DBA can check and kill session

How to find how many open cursor


  select sum(a.value)
 from v$sesstat a, v$statname b, v$session s
 where a.statistic# = b.statistic#
 and s.sid (+)= a.sid

 and b.name = ‘opened cursors current’;

User wise open cursors

select s.username, max(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid (+)= a.sid
and b.name = ‘opened cursors current’
group by s.username
order by 2;


Session wise open cursor


selects.username,s.sid,s.SERIAL#,max(a.value)
fromv$sesstat a, v$statname b,v$session s
wherea.statistic# =b.statistic#
ands.sid (+)= a.sid
andb.name = ‘opened cursors current’
groupby s.username,s.sid,s.SERIAL#
orderby 4 desc;



How to kill session use more open cursor

ALTER SYSTEM KILL SESSION ‘sid,serial#’;


ALTER SYSTEM KILL SESSION ‘2, 9093’;
ALTER SYSTEM KILL SESSION ‘1083, 4249’;
ALTER SYSTEM KILL SESSION ‘1421, 8505’;




    Like Comment and Share the post and Don’t forget to 
   Subscribe IT KEEDA


Leave a Reply

%d bloggers like this: