Tracking Cursors usage

Total cursors open, by session:

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 username = 'FARHAT';

Here's how to find open cursors that have been parsed. You need to be logged in as a user with access to v$open_cursor and v$session.

COLUMN USER_NAME FORMAT A15

SELECT s.machine, oc.user_name, oc.sql_text, count(1)
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2
ORDER BY count(1) DESC
;

This select shows you the actual SQL code that opened the cursors

select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

You can filter your users


select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
WHERE USER_NAME='FARHAT'
group by sql_text, user_name order by count(*) desc;

You can find Sum of Number of Cursors opened by all users connected to database

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
 s.username, s.machine
 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, s.machine
 order by 1 desc;