How to find Current open Cursors in Oracle

Total cursors open, by session:

select a.value, s.username, s.sid, s.serial#from v$sesstat a, v$statname b, v$session swhere a.statistic# = b.statistic#  and s.sid=a.sidand b.name = 'opened cursors current';

Source: http://www.orafaq.com/node/758

As far as I know queries on v$ views are based on pseudo-tables ("x$" tables) that point directly to the relevant portions of the SGA, so you can't get more accurate than that; however this also means that it is point-in-time (i.e. dirty read).

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

appears to work for me.

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 A15SELECT s.machine, oc.user_name, oc.sql_text, count(1) FROM v$open_cursor oc, v$session sWHERE oc.sid = s.sidGROUP BY user_name, sql_text, machineHAVING COUNT(1) > 2ORDER BY count(1) DESC;

If gives you part of the SQL text so it can be useful for identifying leaky applications. If a cursor has not been parsed, then it does not appear here. Note that Oralce will sometimes keep things open longer than you do.