Better At Oracle

A collection of tutorials, code and tools to help you get better using Oracle


09 April 2016

Cursor Leak For Invalid SQL Not In open_cursors

I came across an interesting problem recently where an application had a cursor leak against a piece of SQL that it did not have permission to execute. Normally cursor leaks are pretty easy to spot by querying v$opencursors and grouping by the SQL\ID, but in this case, the cursor never made it into v$open_cursors, but still clocked up an open cursor each time it failed to parse.

For example, this piece of Java code causes the issue when auser does not have select permissions on v_$instance:

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

public class Test {

    public static void main(String[] args)
    throws ClassNotFoundException, SQLException
    {
       DriverManager.registerDriver
           (new oracle.jdbc.OracleDriver());

        String url = "jdbc:oracle:thin:@//192.168.33.8/ora12cr1";

        Connection conn =
        DriverManager.getConnection(url,"auser","auser");

        conn.setAutoCommit(false);
        System.out.println ("about to enter loop"); 
    for (int i=0; i<100; i++) {
            try {
              Thread.sleep(1000);      
              conn.createStatement().execute("select version from v$instance");
            } catch(Exception e) {
              e.printStackTrace(System.out);
            }
    }
    }
}

Assuming open_cursors is set to 50 (the default), this simple Java program will start showing ORA-01000 errors after 50 seconds, but if you query v$open_cursor you won't find a lot of cursors associate with the session, making it more difficult to find.

blog comments powered by Disqus