In writing my application that checks certain settings and values every few seconds received the following error today.
ORA-01000: maximum open cursors
This error presented itself because I wasn’t closing the OracleDataReader object in my code. I’ll show below the entire code and then bold the code that I had to add to rid my code of the leak. I used TOAD for Oracle to find the opened cursors current in the Statistics of the Session Browser on the database.
I am using Oracle 10g and the C# code is as follows:
public int OracleActiveConnections
get // SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL
OracleConnection oraConn = new OracleConnection(“Data Source=main_new_prod;User ID=system;Password=manager;”);
OracleCommand oraCmd = oraConn.CreateCommand();
oraCmd.CommandText = “select count(server) as Value, server from v$session group by server”;
oraCmd.CommandType = System.Data.CommandType.Text;
oraCmd.CommandTimeout = 15;
OracleDataReader oResult = oraCmd.ExecuteReader();
int iValue = Convert.ToInt32(oResult[“Value”].ToString());
Adding the code in bold above closed the object cursors without having to tinker with the max-pooled settings on the datasource or the cursors setting on the database. Although this is something may need to be changed. Test, test test.