Re: ORA-01000: maximum open cursors ...

From: VC <boston103_at_hotmail.com>
Date: Sat, 27 Mar 2004 02:51:33 GMT
Message-ID: <U_59c.101179$1p.1482182@attbi_s54>

Hello,

"Theodor Ramisch" <theodor_ramisch_at_hotmail.com> wrote in message news:406454d5$0$17748$9b4e6d93_at_newsread2.arcor-online.net...
> Hi there,
>
> we are currently working on an ASP.Net Application which
> uses a Oracle 9i database. On some pages we have to do
> about 60 queries to oracle. After loading a few pages,
> the number of cursors increases to 300 and we get the
> error ORA-01000: maximum open cursors exceeded.
>
> Our C# DataReaders are closed after every statement,
> but the connection stays open for the whole session.
>
> Why are the cursors left open after the querie is done?
> Do we have to close and reopen the connection at every page?
>
> Regards,
>
> Theodor Ramisch
>
>

Well, it's quite easy to verify that closing the reader does indeed closes the cursor:

• test C# program:

using System;
using Oracle.DataAccess.Client;

class test {

  static void Main() {
    try {

       OracleConnection conn = new OracleConnection();
       conn.ConnectionString = "User Id=hr;Password=hr;Data Source=orcl";
       conn.Open();
       OracleCommand cmd = new OracleCommand("select owner, object_name from
t1", conn);
       OracleDataReader rdr;
       for (int i=1; i<=10;i++) {
         rdr = cmd.ExecuteReader();
         Console.Write("After ExecuteReader..." + i); Console.ReadLine();
         rdr.Close();
       }
       conn.Close();
       conn.Dispose();
     } catch(Exception ex){
        Console.WriteLine("Exception :" + ex.ToString());
     }

  }

}

If you run the query below after each 'executeReader', you'll see that number of the open cursors remains the same:

select a.value, b.name, a.sid, d.sql_text

    from v$sesstat a, v$statname b, v$session c, v$sql d     where a.statistic# = b.statistic# and b.name = 'opened cursors current'

 and a.sid=c.sid
 and d.hash_value=c.sql_hash_value
 and d.address=c.sql_address
 and d.sql_text like 'select owner%t1%'

• result:

VALUE NAME SID SQL_TEXT
3 opened cursors current 16 select owner , object_name from t1

If you comment out the Close() call, then, naturally, the number of open cursors will grow and after the tenth run it'll be:

VALUE NAME SID SQL_TEXT
12 opened cursors current 16 select owner , object_name from t1

Apparently, two cursors are opened (and kept open for some internal purpose until the connection is closed) and each new Execute opens an additional cursor (which is closed by a corresponding Close() ).

So, the answer is: no, you do not need to close/open the connection but you have to close the readers.

VC Received on Fri Mar 26 2004 - 20:51:33 CST