Monitoring Oracle Max Cursors for JDBC Connections

With Oracle JDBC and a Java application, just closing an application’s DB connection does not destroy the Statement and ResultSet objects (and hence the related cursors are not closed). Even if you are using a connection pool, the connection is returned to the pool, but the cursors may remain open on the server. Ironically - a side-effect of connection pooling, in this case.

A detailed discussion can be found in this article which covers “the difference between open cursors and session cached cursors, their initialization parameters, and how to monitor and tune them.”

Over time, cursors can accumulate. A typical result of exceeding the maximum allowed cursor count is an error such as the following in your Java application:

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1  
ORA-01000: maximum open cursors exceeded  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01000: maximum open cursors exceeded  
ORA-01000: maximum open cursors exceeded

You can investigate these open cursors in the database with a query such as the following:

SQL


1
2
3
4
5
6
7
8


select sum(a.value) total_cur, avg(a.value) avg_cur,
  max(a.value) max_cur, s.username, s.machine  
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'   
and username = 'the_user_id'  
group by s.username, s.machine  
order by 1 desc;

One way to avoid the issue is to ensure Oracle database resources are closed. In a non-pooled configuration, you need to ensure the result set, statement, and connection are all explicitly closed - including in failure scenarios:

Java


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45


try {  
    try {  
        Class.forName("com.teradata.jdbc.TeraDriver");  
    } catch (ClassNotFoundException cnfe) {  
        ; // do what you want here  
    }  
    conn = DriverManager.getConnection(url, user, pass);  
    stmt = conn.createStatement();  
    rs = stmt.executeQuery(query);  
    while (rs.next()) {  
        result = clobToString(rs.getClob("CLOB_CONTENT"));  
       //System.out.println(result);  
    }  
    rs.close();  
    rs = null;  
    stmt.close();  
    stmt = null;  
    conn.close();  
    conn = null;  
} catch (SQLException sqle) {  
    ; // do what you want here  
} finally {  
    if (rs != null) {  
        try {  
            rs.close();  
        } catch (SQLException sqle) {;  
        }  
        rs = null;  
    }  
    if (stmt != null) {  
        try {  
            stmt.close();  
        } catch (SQLException sqle) {;  
        }  
        stmt = null;  
    }  
    if (conn != null) {  
        try {  
            conn.close();  
        } catch (SQLException sqle) {  
            ;  
        }  
        conn = null;  
    }  
}