ORA-01000 max cursors exceeded

[az...@hotmail.com]

Hi

My app is written using the OCI running against a 9i 9.2.0.1.0 server
running on Windows XP SP2.

I (& my users) keep on encountering the hoary old ORA-01000 problem.
The stored procedures in the database use a lot of ref cursors to
return result sets. Where possible, in the stored procedures, I close
the cursors and where they are returned to the application I have tried
a couple of things:

1. I have made my cursors scrollable and then cancelled them by
calling OCIStmtFetch2 with nrows set to 0.

2. I have made my cursors non scrollable and always loop through
fetching til I hit 1403 (NO_DATA), which, according to the
documentation (OCI), implicitly cancels the statement handle.

Either way I still have the cursors I returned from PL/SQL stored
procedures hanging around AND I have the implicit cursors PL/SQL opens
from standard SQL queries.

If I query v$mystat for the number of open cursors it always seems
quite low. I understand that PL/SQL caches its implicit cursors in
such a way that they shouldn't be affecting my overall cursor count so
I just don't understand why I keep running out!

The application communicates with the database almost exclusively by
using stored procedures, so I feel it must be something I am not doing
in the OCI code which isn't freeing up the server side cursor resource.

It seems as though the web is full of people experiencing this problem
but there are very few useful OCI based responses so I hope someone can
help!

Thanks
Alex

[Rauf Sarwar]

What's the value of open_cursors init parameter in your database?

Regards
/Rauf

[az...@hotmail.com]

We have upped it to 1500!! Someone here came up with the idea of
upping it to 2 million!

Alex

[az...@hotmail.com]

I should add that what I am particularly concerned about is that I am
not cancelling/closing the server side cursors properly in my OCI code.

Alex

[Sybrand Bakker]

open_cursors is a *per session* limit.
You may be up to unpleasant surprises if you follow that advice.
In fact, 1500 is already way too much. Usually it stops at 500.

--
Sybrand Bakker, Senior Oracle DBA

[Joel Garry]

O9 OEM Top Sessions has a selection for showing the open cursors in
your session, so you can see exactly what is going on. Right click on
the session and drilldown to open cursors for this session. That's one
way, anyways. Right click on the cursor for an explain plan, FWIW.

I was recently using it to look at an app that had 218 cursors open. I
don't think it was unreasonable, considering. There were certainly a
number that didn't need to be there, and some were quite eye-opening.
I have to take back what I said in another thread about never seeing
small lookup tables anymore.

jg
--
@home.com is bogus.
'Perl is another example of filling a tiny, short-term need, and then
being a real problem in the longer term,' 'Once you have something that
grows faster than education grows, you're always going to get a pop
culture,' 'most undergraduate degrees in computer science these days
are basically Java vocational training,' 'All creativity is an extended
form of a joke,' and 'nobody really knows how to design a good
language.' - Alan Kay per /.

[az...@hotmail.com]

OK - I have sorted the problem.

My handle for my statements was being allocated for me by my call to
OCIStmtPrepare2 - so I could cancel this using OCIStmtFetch2 with nrows
= 0 and OCIStmtRelease.

However, my handle for cursors (used explicitly and solely for result
sets from stored procedures) was a handle I was allocating myself using
OCIHandleAlloc, so calling OCIStmtFetch2 and OCIStmtRelease was a
nonsense. I have changed the code which cancels the cursor handles to
call OCIHandleFree (followed by an OCIHandleAlloc - since I still need
it) - and now I can set my open_cursors to sensible values (as I test
I've set it to 10 - for production I think I'll leave it at 50).

I'm not entirely thrilled about having to free and allocate my cursor
handle all the time but I will live with it for now.

Thanks for everyone's input and I'm glad I've finally sussed this out!

Cheers
Alex