Why Oracle exceeds the maximum number of open cursors and solutions

 Why Oracle exceeds the maximum number of open cursors and solutionsCategory: Oracle -related 2012-06-05 10:36 6362 People read comments (0) favorite reports Oracle Database Sqljavasessionsystem

Handles the maximum number of exceptions that exceed the open cursor (Ora-01000:maximum open cursors exceeded)

Ora-01000_maximum_open_cursors_exceeded_

The Ora-01000:maximum open cursors exceeded exception often occurs 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.executeupdate ();
}

1. Check the open_cursors parameter values in the database.
Oracle uses the initialization parameters in Init.ora open_cursors 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 changed.

Sql> commit;

Submit complete.

Sql> Show parameter open_cursors;

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

It is important to set the value of the open_cursors large enough to prevent the application from exhausting all open cursors. Different applications, this value is also different. The system overhead is not increased even if the number of cursors opened by the session does not reach the number specified by Open_cursors (that is, the value is set higher than is actually required).
2. Gets the number of open cursors.
The following query shows the number of cursors opened by the 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
10 m2
411 M3 10
Test 9
Note that v$open_cursor can track dynamic cursors for parsed and not CLOSED in a session (cursors opened with Dbms_sql.open_cursor ()). It does not track dynamic cursors that are not parsed (but opened). Using dynamic cursors in your application is not uncommon. The premise of this mode is that dynamic cursors are not used.
3. Gets the SQL executed for the cursor.
Run the following query using the SID found in the query results above:
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 so that you can reverse track to the source of the open cursor.


The main reason for such errors to occur easily in Java code is that Java code is actually quite similar to opening a cursor in the database when executing conn.createstatement () and Conn.preparestatement (). In particular, if your createstatement and preparestatement are inside a loop, you will be very prone to this problem. Because the cursor is constantly open and not closed.
In general, when we write Java code, both createstatement and preparestatement should be placed outside the loop, and when these statment are used, they are closed in a timely manner. It is best to close statement or PreparedStatement immediately after performing a executequery, executeupdate, etc., if you do not need to use the result set (ResultSet) data.
for the occurrence of ORA-01000 error in this case, simply increase open_cursors is not a good way, it is only a temporary cure。 In fact, the hidden dangers in the code are not lifted.
And, for the most part, open_cursors only need to set a smaller value, which is enough to use unless there is a very special requirement.
If you do not use the connection pool, then there is no problem, once the connection is closed, the database physical connection is released, all related Java resources can also be collected by GC.
But if you use connection pooling, then note that connection shutdown is not physical shutdown, just returning the connection pool, so preparedstatement and ResultSet are both held and actually occupy the relevant database cursor resources, in which case, long-running , the error "cursor exceeds the maximum allowed for the database" is reported, causing the program to fail to access the database properly.
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.executeupdate ();
prepstmt.close ();
}
after you perform a executequery, executeupdate, and so on, if you do not need to use the result set (ResultSet) data, close the statement or PreparedStatement immediately.

Why Oracle exceeds the maximum number of open cursors and solutions