If you have some legacy database operations in your java code, sometimes your application will stop working after a while. When you look at your server log, you find something like this:
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
...
Usually you can temporarily solve the problem by restarting the server. But it will come back at you when database connection running out of open cursors again.
Root Cause
The issue here is that every database connection can only have a fixed number of open cursors (defined as the maximum open cursors in the Oracle database). When your code run a query statement, it will open a cursor; when you close the statement, the cursor will be closed. However, sometimes programmers want to run two different queries in the same block of code, and they forget
to close the first one before using the second. For example:
PreparedStatement statement = null;
ResultSet result;
try {
// is user an alumnus
statement = connection.prepareStatement(
"SELECT package1.is_alum(?) FROM dual ");
statement.setString(1, _id);
result = statement.executeQuery();
if (result.next()) {
_isAlum = result.getInt(1)==1?true:false;
}
// is user a student
statement = connection.prepareStatement(
"SELECT package1.is_student(?) FROM dual ");
statement.setString(1, _id);
result = statement.executeQuery();
if (result.next()) {
_isStudent = result.getInt(1)==1?true:false;
}
}
finally {
if (statement != null) statement.close();
}
The problem is that even though we close the prepared statement at the end, the first statement to check if user is an alum is never closed - a leaked open cursor!
Debug
So how do you debug such a problem? The server log usually will not tell you where the unclosed statement is. It just informs you that a certain database operation can not run because maximum open cursors are reached. You need to go to the database itself, run a query to find out the SQL statement with the must open cursors:
SELECT s.machine, oc.user_name, oc.sql_text, count(1) cnt
FROM V$OPEN_CURSOR OC, V$SESSION S
WHERE OC.SID = S.SID
GROUP BY USER_NAME, SQL_TEXT, MACHINE
HAVING COUNT(1) > 9
ORDER BY COUNT(1) DESC
You will get a result like this:
machine user_name sql_text cnt
my.host.com ADMIN SELECT package1.is_alum(:1 ) FROM dual 413
Now we know the problem is of this sql statement, so search your java code for a substring of the statement (i.e. search "package1.is_alum" in this case), and then close the statement!
Solution
PreparedStatement statement = null;
ResultSet result;
try {
// is user an alumnus
statement = connection.prepareStatement(
"SELECT package1.is_alum(?) FROM dual ");
statement.setString(1, _id);
result = statement.executeQuery();
if (result.next()) {
_isAlum = result.getInt(1)==1?true:false;
}
if (statement != null) statement.close();
// is user a student
statement = connection.prepareStatement(
"SELECT package1.is_student(?) FROM dual ");
statement.setString(1, _id);
result = statement.executeQuery();
if (result.next()) {
_isStudent = result.getInt(1)==1?true:false;
}
}
finally {
if (statement != null) statement.close();
}
