Oracle Maximum Open Cursors Exceeded

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;”);
oraConn.Open();
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();
oResult.Read();
int iValue = Convert.ToInt32(oResult[“Value”].ToString());
oResult.Close();
oResult.Dispose();

oraCmd.Dispose();
oraConn.Close();
oraConn.Dispose();

return iValue;
}
}

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.

Leave a Reply

%d bloggers like this: