Oracle Open Cursor Check Queries

Query to check if Open Cursors has reached Max limit :

SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur 

FROM v$sesstat a, v$statname b, v$parameter p 

WHERE  a.statistic# = b.statistic#  

and b.name = ‘opened cursors current’ 

and p.name= ‘open_cursors’ 

group by p.value;

 

Query to get count and list of SQL Queries causing Open Cursors :

select  sid , sql_text, count(*) open_cursors,user_name

from v$open_cursor b

group by sid ,sql_text,user_name 

order by 3 desc;

 

Query to get list of Sessions(SID) with Open Cursors Count:

select a.value, s.username, s.sid, s.serial# 

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’ 

and s.username is not null

order by 1 desc;