Hello,
I use jboss2.4.1 with tomcat and oracle 806 and its jdbc driver, somehow I ran into the issue of exceeding the maximum open cursors, which was set to 50. I can reset it to higher number, however there are a few questions puzzle me. Any hints/comments will definitely help me to understand/fix this issue. My questions are:
1) Is ps cache enabled by default? if yes, what's the max and how its set?
2) If the cache is not enabled OR the max cache size is smaller than max open cursors limit, I should not run into the issue, correct? Note: no open cursors inside my store procedure.
3) By monitoring v_$open_cursor on the oracle db, I noticed that some of the opened cursors are never closed. How come? A preparedstatement is created in stateless session beans (actually through a DAO) and close is called in finally block. It seems that all the opened statements should be closed before a connection is put back to the db pool. Correct?
4) It seems that, if ps cache is enabled, wrappedStatement.close() will *not* allow the statement to be closed when the connection is freed and put back to the db pool. Correct? So if users forget to call close on created statements, it won't be an issue unless that one transaction opens quite a lot of cursors.
Thanks,
Jian
