Re: ORA-01000: maximum open cursors exceeded

From: gazzag <gareth_at_jamms.org>
Date: 18 Jan 2007 06:19:19 -0800
Message-ID: <1169129959.860514.162450@q2g2000cwa.googlegroups.com>

adam_at_areasix.co.uk wrote:
> I am recieving this error that has everybody in the company stumped!
> The error is an 'ORA-01000: maximum open cursors exceeded' error. I
> have looked around on the internet and not found any answers.
>
> Basically, all I'm doing is using a VB.NET application to connect to an
> oracle database and run a query from a table I have created - Simply
> SELECT * FROM UPDATEQUEUE WHERE UQ_UPDATED='0'
>
> When I look in the console manager, it looks like there are 300 cursors
> open - but this is the only query I am running.
>
> Does anybody have any suggestions?

In my experience, .Net is notorious for not closing cursors it has opened.

Type the following at a SQL*Plus prompt:

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ -----------
-----------------------------
open_cursors                         integer     300

SQL> The indicates the maximum number of open cursors allowed per session on that database - 300 in my case and yours too, by the sound of it. Either:

1. Re-write the VB code to close any cursors it opens, or
2. Get your DBA to up the open_cursors parameter for that particular database.

Although, why anyone should need more than 300 is beyond me. My preference would be to correct the VB code.

HTH -g Received on Thu Jan 18 2007 - 08:19:19 CST