Menu
What is the query to find the number of current open cursors in an Oracle Instance?
Also, what is the accuracy/update frequency of this data?
I am using Oracle 10gR2
498
You want to display open cursors in Oracle. We can query the data dictionary to determine the number of cursors that are open per session. "V$SESSION" provides a more accurate number of the cursors currently open than "V$OPEN_CURSOR".
The cursor count is per session. The Oracle parameter open_cursors sets the maximum number of cursors per session. A cusror can be thought of as a sql statement. So if 100 cursors are open it would indicate that a process has 100 sql statements open simultaneously.
OPEN cursor_name; In this syntax, the cursor_name is the name of the cursor declared in the declaration section. When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement.
Yes, closing/killing a session will close any related cursors. Don't use DBMS_SHARED_POOL. PURGE!
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).
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
