[SOLVED] Hibernate Error [ORA-01000: maximum open cursors exceeded]

Enterprise application with many persistent cursors need special treatment to hold on. When you load too many HBMs with a cursor each connection, you must also provide persistent connection (especially with pooling mechanism)

[from IBM]
Typically, in Java, when an object goes out of scope, it is automatically garbage collected, but the Java specification does not place any specific requirements on the JVM regarding when (or even if) this will occur for any particular object. Therefore, do not rely on finalizers to close cursors. A finalizer is called when a class is garbage collected.

Explicitly close all cursors (e.g. ResultSet.close() and Statement.close()) when the cursors are no longer needed. This ensures that the corresponding server-side cursors are closed, preventing the ORA-01000 error.

The SQL_TEXT column can be queried in V$OPEN_CURSOR view to determine which cursors are not being closed. For example:

select sql_text from v$open_cursor;

Notes:
1. Typically, close() statements should be put in a finally clause of the try block.
2. There are known problems in certain versions of Oracle 8.1.6 JDBC drivers that can make this problem worse. The Oracle 8.1.7 JDBC driver or a later version should be used.
[end from IBM]

error : ORA-01000: maximum open cursors exceeded will happen on oracle 10 with persistent connection more than 1000. It will not appear on oracle 9 (as i tried on my first laptop). Explicitly you may use oracle 10 OJDBC pointed to oracle 9 to mock it do as oracle 9.

here's the example :

for oracle 10 you may use

<property name="dialect">org.hibernate.dialect.OracleDialect</property>

for oracle 9 you may use

<property name="dialect">org.hibernate.dialect.9OracleDialect</property>

both are use same driver. (ojdbc 14).

Short solution to solve this error is :
1. add to your Hibernate config

<property name="statement_cache.size">0</property>

2. remove your connection pooling

<!--
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">100</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.validate">false</property>
-->

and everything is working now... :D
next question is : "how to update my connection pool so it will work for more than 1000 connections ?"

Let's research about this... :)

note:
check the cursor
SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid

..show all:.

.

Enterprise application with many persistent cursors need special treatment to hold on. When you load too many HBMs with a cursor each connection, you must also provide persistent connection (especially with pooling mechanism)

[from IBM]
Typically, in Java, when an object goes out of scope, it is automatically garbage collected, but the Java specification does not place any specific requirements on the JVM regarding when (or even if) this will occur for any particular object. Therefore, do not rely on finalizers to close cursors. A finalizer is called when a class is garbage collected.

Explicitly close all cursors (e.g. ResultSet.close() and Statement.close()) when the cursors are no longer needed. This ensures that the corresponding server-side cursors are closed, preventing the ORA-01000 error.

The SQL_TEXT column can be queried in V$OPEN_CURSOR view to determine which cursors are not being closed. For example:

select sql_text from v$open_cursor;

Notes:
1. Typically, close() statements should be put in a finally clause of the try block.
2. There are known problems in certain versions of Oracle 8.1.6 JDBC drivers that can make this problem worse. The Oracle 8.1.7 JDBC driver or a later version should be used.
[end from IBM]

error : ORA-01000: maximum open cursors exceeded will happen on oracle 10 with persistent connection more than 1000. It will not appear on oracle 9 (as i tried on my first laptop). Explicitly you may use oracle 10 OJDBC pointed to oracle 9 to mock it do as oracle 9.

here's the example :

for oracle 10 you may use

<property name="dialect">org.hibernate.dialect.OracleDialect</property>

for oracle 9 you may use

<property name="dialect">org.hibernate.dialect.9OracleDialect</property>

both are use same driver. (ojdbc 14).

Short solution to solve this error is :
1. add to your Hibernate config

<property name="statement_cache.size">0</property>

2. remove your connection pooling

<!--
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">100</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.validate">false</property>
-->

and everything is working now... :D
next question is : "how to update my connection pool so it will work for more than 1000 connections ?"

Let's research about this... :)

note:
check the cursor
SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid