ORA-01000: maximum open cursors exceeded

 

Oracle uses the OPEN_CURSORS parameter to specify the maximum number of open cursors a session can have at once. When this number is exceeded, Oracle reports an ORA-01000 error. When this error is propagated to WebLogic Server, a SQLException is thrown.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

to resolve ORA-01000, try to close whatever cursors are no longer in use, raise the OPEN_CURSORS parameter within your initialization file, and restart Oracle.

ORA-01000 is thrown because of a maxed limit of open cursors in an execution or user session. Working around ORA-01000 can be done on the database level by setting the init.ora parameter, OPEN_CURSORS, which states the maximum cursors allowed. Note that the OPEN_CURSORS are defaulted at 50 which may not be high enough, causing ORA-01000, and that each operating system determines the appropriate value.

Resolving ORA-01000 try setting this OPEN_CURSORS to a higher number. Keep in mind when resolving ORA-01000 in this way that changing this value in the proper way should not alter the system performance, but will probably require more memory for cursor storing.
Before setting a new limit we should debug the root cause of this kind of issue and should check it’s not a development code issue, then only we shall go ahead with changing the MAX open cursors limit to some new value.

To debug and determine the issue we can follow below steps:

Login to database as an administrator or your user needs permissions to select from those v$ views.

— Check OPEN_CURSORS parameter value in your database.

Oracle uses the initialization parameter OPEN_CURSORS in init.ora to specify the maximum number of cursors a session can have at once. The default value is 50. Unfortunately, this default value is usually too small for systems such as WebLogic Server. To find out the value of OPEN_CURSORS parameter in your database, you may use the following query:

SQL> show parameter open_cursors;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1200

It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.

— Get number of open cursors.

The query below shows number of open cursors for each session opened by user ‘SCOTT’ in descending order.

SQL> select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where user_name = 'SCOTT' and o.sid=s.sid
group by o.sid, osuser, machine
order by num_curs desc;

SID OSUSER MACHINE NUM_CURS
---------- ---------------- ------------------------------------------------- ----------
213 m1 1000
196 m2 10
421 m3 10
150 m4 9

When you use connection pool in WebLogic Server, the user_name in this query should be the user_name you use to create the connection pool, assuming that the connection is retrieved from a connection pool. The query result also gives machine names. From the query result, identify SIDs with high number of open cursors and machine names on which you run your WebLogic Servers.

Please note that v$open_cursor can track dynamic cursors PARSED and NOT CLOSED over a session.This pattern assumes that dynamic cursors are not used.

— Get the SQL being executed for the cursors.

Take the SID identified from the above query result and run the following query:

SQL> select q.sql_text
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.sid = 213;

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------
select * from empsal where empid='213'
select * from empsal where empid='421'
select * from empsal where empid='196'
select * from empsal where empid='527'

The result shows what queries are being executed on the connection.

From this you can trace back to see where is the source of open cursors.

Safe code practice avoid the issue:

– The most common cause of this problem is that JDBC objects are not properly closed.
– Use the result from the above sql queries to trace back in application code to make sure that all JDBC objects are properly closed.
– Avoid any code practice that abandons JDBC objects.
– Make sure Statements and ResultSets are closed when a Connection is closed.If you don’t explicitly close Statements and ResultSets right away, cursors may accumulate and exceed the maximum number allowed in your DB before the Connection is closed.

Weblogic related measures to avoid the issue:

– When you use a prepared statement or callable statement in an application or EJB, there is considerable processing overhead for the communication between the application server and the database server and on the database server itself. To minimize the processing costs, WebLogic Server can cache prepared and callable statements used in your applications.

– When WebLogic Server caches a prepared or callable statement, in many cases, the DBMS will maintain a cursor for each open statement. Hence, statement caching could be a source of the “maximum open cursors exceeded” problem.

– To determine whether the “maximum open cursors exceeded” problem is related to statement caching, you may turn this feature off by setting statement cache size to 0 or reduce the cache size to see whether you still get the error.
Note: Another cause of the “maximum open cursors exceeded” problem could be a JDBC driver problem.You need to try different driver versions to check if the same issue is observed.

 

 

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.