ORA-01000: maximum open cursors exceeded using oracle instant client and C#
c#, oracle
Solution
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.
Problem
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?