Re: Maximum open cursors

OCI call, oclose() will close the cursor. Sometimes middlewares do not close cursors (for performance reasons). For e.g., in ODBC, the SQL_CLOSE option does not close the cursor, SQL_DROP does. Hence it may be advisable for you to check the software layer accessing OCI... Is it actually calling oclose() or just faking it?

Also, if the layer is written in OCI, ignore Pro*C totally. HOLD_CURSORS etc are specific only to Pro*C. OPEN_CURSORS enables the dba to specify the max cursors which can be opened by any user session. Increasing this value will allow the program to function... however, that may not be the solution for the long run. You need to check as to why the cursors are not being closed.

-sm

On Mon, 15 Sep 1997 17:09:36 +0200, Alon Gilad <alon_at_rts.co.il> wrote:

>Hello to all ,
>
>I'm running an application that interfaces Oracle using OCI. From time
>to time I get the "MAX open Cursors" error. When looking at the number
>of open cursors my application has open I see that even though I close a
>cursor Oracle does not release the links to the cursor.
>
>To check the number of open cursors I run:
> select c.sid, substr(s.osuser,1,15) "user", s.process,
> substr(s.machine,1,15) machine, count(*)
> from v$open_cursor c, v$session s
> where c.sid = s.sid
> group by c.sid, substr(s.osuser,1,15),
> s.process, substr(s.machine,1,15)
> order by count(*)
>
>Oracle support said that in Pro C there are two parameters that
>determine if Oracle releases the cursors or not : RELEASE_CURSOR,
>HOLD_CURSOR .
>
>HOLD_CURSOR by default is NO. This means that after Oracle executes a
>SQL
>statement the links to the cursor cache, memory, and parse locks are
>released
>and marked for reuse.
>
>RELEASE_CURSOR by default is NO. This means that after Oracle executes a
>SQL
>statement, the links to the cursor cache is maintained and not released.
>
>As for OCI they said all I can play with is the "OPEN_CURSORS" parameter
>in the Oracle ini file. Does anybody know of any way to affect Oracle
>behavior at the application level ?
>
>Alon
Received on Tue Sep 16 1997 - 00:00:00 CDT