Counting the number of open cursors in a database instance can be a confusing activity. Perhaps an application has received an "ORA-01000: maximum open cursors exceeded" error message or perhaps you simply want to get an idea of how many cursors an application has open at some point in its execution. No matter the reason, you've attempted to determine the number of open cursors and have found what seems like incorrect or confusing results (i.e. the value is too high!).
Your first inclination may be to simply execute a query such as the following to determine how many cursors are currently open in an instance:
select count(*) from v$open_cursor
- or -
select sid, count(*) from v$open_cursor group by sid order by sid
This seems like a perfectly reasonable thing to do given that you want to find out how many open cursors there are. However, it is just about a guarantee that this query will not give you what you really want. Why is this so? Some reasons for this are:
- Sessions that are no longer connected can be reported
- Cursors that are closed as far as the application is concerned but are cached on the server side can be reported (for example, by using release_cursor=no in Pro*C/C++)
- Cursors cached by PL/SQL can be counted
- Cursors from recursive SQL can be counted
In order to get a more representative value for the number of actual open cursors, try a query such as this:
select a.sid,
a.value,
b.name
from v$sesstat a,
v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and a.value != 0
order by a.sid
This query should be a good starting point and can be altered as necessary to suit your needs.
