Open oracle cursor reaching limit after session.close()

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.

-Tony

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s