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:
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: