How to find Current open Cursors in Oracle

oracle oracle10g cursor
M.N picture M.N · Jun 18, 2009 · Viewed 183k times · Source

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

Jeffrey Kemp picture Jeffrey Kemp · Jun 18, 2009

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

Related questions

When should I use 'for update nowait' in cursors?

In which case do we need to use for update nowait in cursors.

Read More
How to find the privileges and roles granted to a user in Oracle?

I am using Linux, Oracle10g. I have created one user called test. and granted create session and select any dictionary permission to the same user. i also granted sysdba and sysoper roles to the same users. Now i want …

Read More
Checking oracle sid and database name

I want to check SID and current database name. I am using following query for checking oracle SID select instance from v$thread; but table or view does not exist error is coming. I am using following query for checking …

Read More