ORA-01000_maximum_open_cursors_exceeded _ causes and solutions for exceeding the maximum number of opened cursors

 

Handle an exception that exceeds the maximum number of opened curlers (ORA-01000: maximum open cursors exceeded)

 

Ora-01000: maximum open cursors exceeded exceptions often occur when you execute 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.exe cuteupdate ();
}

1. Check the open_cursors parameter value 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 a time. The default value is 50. To obtain 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 been changed.

SQL> commit;

Submitted.

SQL> show parameter open_cursors;

Name type value
--------------------------------------------------------------------
Open_cursors integer 1000

It is important to set the value of open_cursors to be large enough to prevent the application from exhausting all open cursors. The value varies with applications. Even if the number of cursors opened by a session does not reach the value specified by open_cursors (that is, the set value is higher than the actual value), the system overhead is not increased.
2. Obtain the number of opened cursors.
The following query displays the number of cursors opened by 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
96 m2 10
411 m3 10
50 test 9
Note that V $ open_cursor can track the dynamic cursors of parsed and not closed in a session (using the cursor opened by dbms_ SQL .open_cursor ). It does not track dynamic cursors that are not analyzed (but opened. Dynamic cursors are not common in applications. The premise of this mode is that no dynamic cursor is used.
3. Obtain the SQL statement executed for the cursor.
Run the following query using the Sid found in the preceding query results:
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 = '201312'
Select * From empdemo where empid = '201312'
Select * From empdemo where empid = '201312'
Select * From empdemo where empid = '201312'
...
The result shows the query being executed on the connection. It provides a starting point, allowing you to track the source of the opened cursor in reverse.

The main cause of this error is that the Java code is executing Conn. createstatement () and conn. when preparestatement () is used, it is equivalent to opening a cursor in the database. In particular, if your createstatement and preparestatement are in a loop, this issue is very likely to occur. Because the cursor is always open and not closed.
In general, when writing Java code, both createstatement and preparestatement should be placed outside the loop and closed in time after these statment are used. It is best to disable statement or preparedstatement immediately after executing executequery, executeupdate, and so on without using resultset data.
For the emergence of ORA-01000 errors in this case, simply increase open_cursors is not a good way, it is just a temporary cure. In fact, the hidden risks in the Code have not been removed.
In most cases, open_cursors only needs to set a small value, which is enough unless there are special requirements.
If you do not use the connection pool, there is no problem. Once the connection is closed, the physical connection of the database is released, and all related Java resources can be recycled by GC.
However, if you use a connection pool, please note that the connection is not physically closed, but the connection pool is returned. Therefore, both preparedstatement and resultset are held and actually occupy the cursor resources of the relevant database, in this case, as long as the program runs for a long time, the error "the cursor exceeds the maximum allowed value of the Database" is often reported, causing the program to fail to access the database normally.
The correct code is 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.exe cuteupdate ();
Prepstmt. Close ();
}
After executing an executequery or executeupdate task, if you do not need to use the resultset data, disable the statement or preparedstatement immediately.