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

Question

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?

Answer 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.

Answer 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.