How to find Current open Cursors in Oracle

Question

What is the query to find the no. of current open cursors in an Oracle Instance?

Also, what is the accuracy/update frequency of this data?

---

I am using Oracle 10gR2

Answer 1

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

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).

Answer 2

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
;

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.

Answer 3

can i able to get the full query.Even with this partial query i can able to figure out the problematic query.To prove this i need full quesry can you post the quesry or ways to get that. Thanks for your query.