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


JOY DUTTA

नमस्ते _/\_ to all . This is JOY DUTTA working in Spark Minda group as a IT Engg from last 4 years , I completed my Diploma from Goverment Polytechnic Dehradun in Computer Science And Engg in 2011.Well, above i written my Qualification and My JOB Role. now i am tell you about Who am i as a person. I am a fun loving guy who love Acting, Dancing, Hangout with friends, I believe in Smart work Rather Than Hard work. I love computers because I love Computers :)In short I am interesting guy if you want reveals more about me You should to meet me. if that not possibleMail me :- joydutta14@gmail.com or Join me on Facebook:- https://www.facebook.com/joydutta.me

Leave a Reply

%d bloggers like this: