Max Cursor Limit Exceeded

[John]

Hi;

We have a .NET application that is calling stored procedures in an Oracle 8i database. Our stored procedures return data via cursors. After our application has been running our stored procedure calls fail and return "...max cursors exceeded." The database has a max cursor setting of 2000. Does anyone have any idea why we are hitting the max cursor limit? I know that we have no where near 2000 concurrent connections, why are the cursors not being freed? Any help is greatly appreciated!

Thank you!!!


John

[olivier.rx]

I exactly the same problem a long time ago. Some Oracle Sessions are not released after Transaction.Commit and Connexion.Close.
The only solution I found is to kill all not used sessions from my application when I detectect that this error type is occured, as system user:

SELECT
S.SID,
S.paddr,
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid
ORDER BY 11
DESC


ALTER SYSTEM DISCONNECT SESSION '9,3' IMMEDIATE --(SID,SERIAL)


But I'm looking for another solution, not so dirty!!!

[Oleg]

Please check that you close your OracleDataReader objects and call Dispose for your OracleCommand.

[John]

Hi Oliver;

Thank you for the tip. I would think that there is some mechanism in Oracle to disable cursor caching. Unfortunately, I am not well versed in the workings of Oracle...

Hi Oleg;

We have double checked all of the code in our application to make sure that all objects are 'closed' and that the 'dispose' is called on the commands. Unfortunately, we are still having the problem.

Thanks!


John

[John]

Hi Oliver;

Actually, I think this problem is resolved by calling .Dispose() on the command object. I must appologise - Oleg suggested this however I had replied before testing - it should work okay now...

You can find a more detailed description of the problem here:
http://www.codeproject.com/dotnet/OracleCursor.asp

Thanks!


John

[olivier.rx]

Thanks for these new informations. I will try to call the dispose fonction for each command, but, what a big job for my business applications!!!

I think that the explicit calls of the methods "Dispose" on the objects, are not a normal solution, nor advised. The classes must manage them self the release of their resources and their finalisations task, to allow the garbage collector to do its cleaning tasks when it collects (in calling the finalisator from the class). It's true that we don't know when the GC will collect, but we can set what I will have to do.

It is a pity that theses "classic solutions":
datareader.close
or even connection.close, connection = nothing, command = nothing
are not enough to release the Oracle open cursors.

What I does not understand, it is how the particular problem with Oracle (not Sql server, cf. http://www.codeproject.com/dotnet/OracleCursor.asp) would be related to the use of a net (C #) technology, and not an other one.
Is this with saying that no Oracle managed (.net) driver could avoid this problem? That's what I don't undestand,because Corelab OraDirect use the Oracle Call Interface API from Oracle client, like the oledb drivers them self.

Sorry for my bad english, I'm french.

[Oleg]

Please download OraDirect .NET 2.50 beta, it partly solve the problem.

[Roel Schreurs]

Could someone explain the reason for having to close the command object. In my view, a DataReader.Close() should close the cursor. I could create a command for a stored procedure and then execute it many times, for instance with different parameter values, yielding many cursors, none of which I want to retain after having Read them to the end. Know I am obliged to Dispose the command every no and then, or to be practical, after each ExecuteReader.

[Oleg]

This problem was solved in OraDirect .NET 2.50.

[Guest]

Thanks for your reply. So it was indeed considered a problem. I'll try to migrate to the newer version.