ORA-01000 maximum open cursors exceeded with Spring data access

1
votes

I am getting ora-01000 maximum open cursors exceeded error in my application while executing queries on Oracle 11g database. I went through some blogs reading about it and found that: It is caused if application open more connections than the value specified in OPEN_CURSOR property of Oracle. The cursors get opened for ResultSet and PreparedStatements.

The best solution to this is to find where the connections are open and not closed (probably findbug can help on this).

I am using Spring data access and JdbcTemplate in that, which manages all these opening and closing of connections, resultset, statements etc. Also connection pooling is used.

My query is that if Spring is managing all the connection handling(opening and closing) then how can I in my application find open connections as I am not the one managing it.

Please give me some hints on what all can I check for.

2
springoracleoracle11gspring-data
A good description of this is given in stackoverflow.com/questions/12192592/… , but I am more interested in how can I find open connections in my application in which connections are handled by Spring – user1630693

2 Answers

2
votes

You can debug if and where you leak PreparedStatements

  1. make a heap dump (VisualVM, jcmd GC.heap_dump, jmap, …)
  2. load the heap dump into Eclipse MAT
  3. look at the number of T4CPreparedStatement instances, if there are several hundreds you have a leak
  4. if you have a leak run the following OQL Query SELECT DISTINC toString(oracle_sql.value) FROM oracle.jdbc.driver.OracleSql
  5. export the result into CSV and import it into SQLite Manager , there the result can be postprocessed, trailing characters can be removed and the queries can be grouped and counted.

I wrote an article about Debugging Oracle PreparedStatement Leaks.

0
votes

maybe that decription is bit outdated. Finally witn Java7 you can control resouce lifecycle by using try-with-resources construct. https://blogs.oracle.com/WebLogicServer/entry/using_try_with_resources_with. Not only you have problems with openning/closing of connections. You also should close ResultSets

In Oracle you can easily find the last SQL executed on the connection by joining v$sql, V$session and v$open_cursor see this answer. When you know which SQL leaked db resources, you can find place in your source code where problem resides.