v$open_cursor can be misleading -- it is cursors that have been opened at some point and may (or may not be) still open. It is useful in helping to track down cursor leaks -- but it shows you more then just "really truely open" cursors.

A better query is something like:


ResultSet rset =
stmt.executeQuery
( "select a.value, b.name "+
"from v$mystat a, v$statname b "+
"where a.statistic# = b.statistic# "+
"and a.statistic#= 3");

That'll tell you how many cursors you really truely have open... Consider this example (get_cur opens "select * from emp" in the stored procedure as a ref cursor)

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;


class curvar
{
public static void showOpenCnt( Connection conn, String msg)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery
( "select a.value, b.name "+
"from v$mystat a, v$statname b "+
"where a.statistic# = b.statistic# "+
"and a.statistic#= 3");
System.out.println( msg );

while( rset.next() )
System.out.println( rset.getString(1)+ " " +
rset.getString(2) );
System.out.println( "-----------------------" );
rset.close();
stmt.close();
}


public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query =
"begin demo_pkg.get_cur( :1 ); end;";

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=
DriverManager.getConnection
("jdbc:oracle:oci8:@ora8idev",
"scott", "tiger");

showOpenCnt( conn, "Before Anything" );

CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();

showOpenCnt( conn, "After prepare and execute" );

ResultSet rset = (ResultSet)cstmt.getObject(1);
showOpenCnt( conn,
"After prepare and execute and getObject" );

for(int i = 0; rset.next(); i++ )
{
if ( i == 10 )
showOpenCnt(conn,"After fetching 10 rows...");
}

showOpenCnt( conn, "After but before any close" );

rset.close();
showOpenCnt( conn, "After rset.close" );

cstmt.close();
showOpenCnt( conn, "After cstmt.close" );
}
}


When I run it (with thick or thin, tried both) I get:

$ java curvar
Before Anything
1 opened cursors current
-----------------------
After prepare and execute
3 opened cursors current
-----------------------
After prepare and execute and getObject
3 opened cursors current
-----------------------
After fetching 10 rows...
3 opened cursors current
-----------------------
After but before any close
2 opened cursors current
-----------------------
After rset.close
2 opened cursors current
-----------------------
After cstmt.close
1 opened cursors current
-----------------------

I'll ALWAYS have 1 cursor open (the query itself to show the count) but note how we get 2 cursors (3) after the prepare and execute. 1 for the begin ... end; block and one for the select we open in the procedure. As we are fetching rows -- we still have 2 application cursors plus 1 for the v$mystat query.

Notice what happens when we exhaust the result set though -- the cursor for the query seems to have disappeared (it did but don't count on it) before we closed it. After we close it the cursor count does not change but after we close the begin ... end; block -- we get back to no cursors open.

follow to comment one

I cannot find any note 48871.996 (that doesn't look like valid note id?) in meta link but no worry.

You say
"This is because the statistic 'opened cursors current' displays the TOTAL number of opened cursors for the session, not necessarily what is currently 'open.'"


but if that were true, my example would show a continously INCREASING number wouldn't it? (yes, that was a rhetorical question).


Rather, my example shows the number of cursors opened going up and down (as we expect).


The issue is v$open_cursor will show you MORE open cursors then you really have (due to some cursor caching done by plsql and other environments). You need to goto v$sessstat to get the REAL currently opened number of cursors.

There may be cursors in v$open_cursor that are not counted in the v$sesstat view for stat #3, they will be really closed if we need those slots again.

I stand by this example and what it shows. If you want to know if you have a cursor leak, use the code above.