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;
Advertisements