select * from V$open_cursor where user_name=;
to identify the sql, that causes trouble.
In my case it was my own code.
You should close all statments and resultset as soon as you finish using it. The finally block only closes the last one you used. If you have a while loop accssing a database table, you need to close your statement and resultset within the while loop.
I ran into the same issues (maximum open cursors exceeded) using oracle with open cursors set to 50. I am very positive that all the statements are closed properly. What I noticed, by looking at v_$open_cursor view, is that the open cursors do not drop even there are no hits to the container. When more hits come in, the number of open cursors start to increase and sometimes the number drops, but overall, the number grow.
Likely the close call on statement does not close the statement. Instead it's put back to the cache and ready for reuse. I assume that these cached statements will be closed before the connection is put back to the dbpool. If my assumption is correct, then I should not see open cursors at all if no hits on container for a while. Correct?