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

User · Answer

This could work  SELECT    sql text  quot SQL Query quot              Count    AS  quot Open Cursors quot   FROM      v open cursor  GROUP BY  sql text  HAVING    Count     gt  2  ORDER BY  Count    DESC

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I use something like this   select    user name     count    as  OPEN CURSORS   from    v open cursor  group by    user name

User · Answer

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   gt  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

User · Answer

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

[oracle] How to find Current open Cursors in Oracle

Examples related to oracle

Examples related to oracle10g

Examples related to cursor