In our web application we are running into this issue very often
ORA-01000 maximum open cursors exceeded
I understand this has to do with the code not closing the cursors (ResultSet, PreparedStatement, etc.) properly.
Sample:
public List<Employee> getAllEmployees() throws DatabaseException{
Connection conn = DatabaseHelper.getConnection(); //Get a connection from the connection pool
PreparedStatement pst = null;
ResultSet rs = null;
List<Employee> emps = new ArrayList<Employee>();
try {
pst = conn.prepareStatement("SELECT * FROM EMPLOYEE ORDER BY EMP_ID");
rs = pst.executeQuery();
while(rs.next()) {
//Do something
}
} catch (SQLException e) {
logger.error("ERROR getting all employees", e);
throw new DatabaseException("Could not get all employees due to an internal error", e);
} finally {
try { pst.close(); } catch(Exception ignore) {} //Close the prepared statement
try { rs.close(); } catch(Exception ignore) {} //Close the Resultset
try { conn.close(); } catch(Exception ignore) {} ////Close the connection, thus returning it to the pool
}
return emps;
}
Though I understand that I'm closing the cursors and the connection in the finally block, what I don't understand is how can I actually verify that the cursors are closed in the Oracle database?
I am currently running the following query (assuming my DB username is EMP)
select * from v$open_cursor where sid in (select sid from v$session where username='EMP' and program ='JDBC Thin Client');
This gives me a list of rows for open cursors for sessions connected through the JDBC thin client. But even after the pst.close(), rs.close() and the conn.close() are called, the query keeps returning the same rows, which seems to indicate that the cursors are still open.
I used JProfiler to check for leaks. However, it tells me if I'm closing the Connection objects or not, but does not tell me about PreparedStatement, Statement and ResultSet
I have already referred to the following post, and have followed the instructions, but none of them seem to answer my question, which is, how can I verify if a cursor is being closed in Oracle (using SQL Developer or some other tool)?
