By default, oracle 10 has the cursor counts set at 1000 if not changed by DBA. and Hibernate will not close them if you simply do session.close(). Can you believe that? ! you would think if a “session” closes, its resources should all be closed. That’s why session in hibernate can’t be regarded literally, such as a SQL*Plus session. In this case, the session behaves more like connection.
Any how, we had to insert more than 5000 rows to a single table and we got ORA error 1000, cursor reached the limit. Following this nice advice, http://javacolors.blogspot.com/2006/10/hibernate-3-and-oracle-9i-cursors-how.html
We had to change code so that we will close sessionFactory after certain amount of inserts. That way, we will ensure cursors will be closed before reaching the 1000 mark.
Another way is to set zero for the prep stmt cache, but that will be at system level to disable this nice feature.
Thanks for reading.