select sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;
appears to work for me.
I use something like this:
select
user_name,
count(*) as "OPEN CURSORS"
from
v$open_cursor
group by
user_name;
This could work:
SELECT sql_text "SQL Query",
Count(*) AS "Open Cursors"
FROM v$open_cursor
GROUP BY sql_text
HAVING Count(*) > 2
ORDER BY Count(*) DESC;
1)your id should have sys dba access 2)
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;
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.
Oracle has a page for this issue with SQL and trouble shooting suggestions.
"Troubleshooting Open Cursor Issues" http://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352
Source: Stackoverflow.com