Reasons for Oracle exceeding the maximum number of open cursors and solutions __oracle

Handle exceeding the maximum number of open cursors exception (Ora-01000:maximum open cursors exceeded)

Ora-01000_maximum_open_cursors_exceeded_

The Ora-01000:maximum open cursors exceeded exception is often encountered when executing the following code
for (int i=0;i<balancelist.size (); i++)
{
prepstmt = Conn.preparestatement (Sql[i]);
Prepstmt.setbigdecimal (1,nb.getrealcost ());
Prepstmt.setstring (2, adclient_id);
Prepstmt.setstring (3, DAYSTR);
Prepstmt.setint (4, comstatic.portalid);
Prepstmt.executeupdate ();
}

1. Check the open_cursors parameter values in the database.
Oracle uses the initialization parameter Open_cursors in Init.ora to specify the maximum number of cursors a session can have at one time. The default value is 50. To get the value of the Open_cursors parameter in the database, you can use the following query:
Sql> Show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ---------------
Open_cursors Integer 300

Modify Open_cursors

Sql> alter system set open_cursors=1000;

The system has changed.

Sql> commit;

Submit completed.

Sql> Show parameter open_cursors;

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
Open_cursors Integer 1000

It is important to set the value of the open_cursors sufficiently large to prevent the application from exhausting all open cursors. The value is different for the application. The system overhead is not increased even if the number of cursors opened by the session does not reach the open_cursors specified (that is, the value set is higher than the value actually needed).
2. Gets the number of cursors opened.
The following query displays the number of cursors opened by user "SCOTT" for each session in descending order.
Sql> Select O.sid, Osuser, Machine, COUNT (*) num_curs
2 from V$open_cursor O, v$session s
3 Where user_name = ' SCOTT ' and O.sid=s.sid
4 GROUP by O.sid, Osuser, machine
5 ORDER BY num_curs Desc;
SID Osuser MACHINE num_curs
-----------------------------------------------------
217 M1 1000
M2 10
411 M3 10
9 test
Note that v$open_cursor can track dynamic cursors for parsed and not CLOSED in a session (cursors opened using Dbms_sql.open_cursor ()). It does not track dynamic cursors that are not parsed (but are open). It is not uncommon to use dynamic cursors in your applications. This mode is premised on the assumption that no dynamic cursors are used.
3. Gets the SQL executed for the cursor.
Run the following query using the SID found in the results of the above query:
Sql> Select Q.sql_text
2 from V$open_cursor o, V$sql q
3 where q.hash_value=o.hash_value and o.sid = 217;
Sql_text
SELECT * from Empdemo where empid= ' 212 '
SELECT * from Empdemo where empid= ' 321 '
SELECT * from Empdemo where empid= ' 947 '
SELECT * from Empdemo where empid= ' 527 '
...
The result displays the query that is executing on the connection. It provides a starting point that allows you to reverse trace to the source of the open cursor.


The main reason such bugs are easy to appear in Java code is that Java code is actually fairly open to a cursor in the database when executing conn.createstatement () and Conn.preparestatement (). In particular, if your createstatement and preparestatement are in a loop, this problem will be very easy to come by. Because the cursor is constantly open and not closed.
In general, when we write Java code, createstatement and preparestatement should be placed outside the loop, and after using these statment, shut down in time. It is best to close the statement or PreparedStatement immediately after performing a executequery, executeupdate, and so on, if you do not need to use data from the result set (ResultSet).
For the occurrence of ORA-01000 error This situation, simple to increase open_cursors is not a good way, it is only a symptom not a cure. In fact, the hidden trouble in the code has not been lifted.
Moreover, in most cases, open_cursors only need to set a relatively small value, is sufficient to use, unless there are very special requirements.
If you do not use a connection pool, then there is no problem, once the connection shutdown, the database physical connection is released, all the relevant Java resources can also be collected by GC.
But if you use a connection pool, then note that the connection shutdown is not physically closed, but only the connection pool is returned, so PreparedStatement and ResultSet are both held and actually occupy the relevant database cursor resources, in which case long running , the "cursor exceeds the maximum allowable size of database" error is often reported, causing the program to not access the database properly.
The correct code, as follows:
for (int i=0;i<balancelist.size (); i++)
{
prepstmt = Conn.preparestatement (Sql[i]);
Prepstmt.setbigdecimal (1,nb.getrealcost ());
Prepstmt.setstring (2, adclient_id);
Prepstmt.setstring (3, DAYSTR);
Prepstmt.setint (4, comstatic.portalid);
Prepstmt.executeupdate ();
prepstmt.close ();
}
after performing a executequery, executeupdate, and so on, if you do not need to use data from the result set (RESULTSET), close statement or PreparedStatement immediately.