'ORA-01000: maximum open cursors exceeded using oracle instant client and C#

Our C# application is generating ORA-01000 error. We were using ODP.Net and the application was running just perfect without any ORA-01000 error.

We removed the ODP.Net and installed the instant client instead (we are now connecting from C# to Oracle using 7 dll files which are oci.dll, ociw32.dll, Oracle.DataAccess.dll, orannzsbb11.dll, oraocci11.dll, oraociicus11.dll and OraOps11w.dll).

Since then we are getting the ORA-01000 upon executing a long process with multiple queries. Noting that we are closing/disposing the OracleDataReader object.

What could be the reason behind the ORA-01000 with instant client?

c++sharporacle


Solution 1:[1]

I know this question is a year old but we just came across the very same issue. All of our Oracle-related objects were closed and disposed and still cursors leaked.

The problem appears to be how ODP.Net uses connection pooling. Connection pooling is on by default and on our environment this consistently appears to cause leaked cursors, which happens repeatedly until the DB decides enough is enough and refuses to grant any more.

The workaround is to disable connection pooling in the connection string, like this:

Data Source=myOracle;User Id=myUsername;Password=myPassword;Pooling=False;

Our application used over 30 cursors on startup and steadily increased to 200. Now that connection pooling is disabled it uses between 2 and 3 cursors.

Solution 2:[2]

Check limit of open cursors with this query:

SELECT NAME, VALUE DISPLAY_VALUE, ISDEFAULT
FROM V$PARAMETER
WHERE NAME = 'open_cursors';

The default value is 50 which is fairly low, in my opinion. See documentation: OPEN_CURSORS

Ask your DBA to increase it, according documentation there is no added overhead to setting this value higher than actually needed.

I just checked my database with

SELECT COUNT(*), user_name 
FROM V$OPEN_CURSOR
WHERE user_name = 'SYS'
GROUP BY user_name;

640 open cursors only by SYS (peak 113 in one session), i.e. not caused by my (potentially bad) application.

Solution 3:[3]

In our company we have been struggling with this problem for almost two weeks. Everything that was disposable was released correctly, but it didn't help: the ORA-01000 problem persisted.

Suddenly we realized: in some stored procedures, that were called very frequently, the following code was executed: dbms_sql.open_cursor

The dangerous thing about this function is that it creates a new cursor of which only the handle is returned in the form of a number. After leaving the scope, Oracle cannot recognize that there is a handle behind this number, which must be released. The consequence was that a new cursor was created each time dbms_sql.open_cursor was called, which remained open as long as the session was active. Since we were using session pooling, these cursors remained open for a very, very long time.

Hope this hint helps.

Solution 4:[4]

Oracle docs note this about ORA-01000:

ORA-01000 maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

In my case it were unmanaged resources such as OracleCommand, OracleRefCursor, OracleGlobalization and OracleDataReader. To minimize the number of open server cursors, explicitly dispose OracleCommand, OracleRefCursor, OracleGlobalization and OracleDataReader objects.

Solution 5:[5]

The only reason of the ORA-01000 is too many opened cursors :)

Check V$OPEN_CURSOR view and see, which cursors are leave opened. Knowing it, you'll be able to determine, why they are still opened.

Solution 6:[6]

The problem is caused by some cursors opened but not closed.

Two problems may cause it (at least as, that's what I see). Either some explicitly opened cursors in strored procedures or DataReader not closd in .Net application.

Solution 7:[7]

Got the same problem and solved it by doing

OracleConnection.ClearPool(oraConn);

between your multiple requests

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nige Jones
Solution 2
Solution 3 Peter Perot
Solution 4 Jeff
Solution 5 Sanders the Softwarer
Solution 6 Thomas Carlton
Solution 7 Goren

Related Questions