Max Cursor Limit Exceeded

Max Cursor Limit Exceeded

Postby John » Fri 12 Nov 2004 21:51

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
John
 
Posts: 25
Joined: Fri 12 Nov 2004 21:18
Location: Durham, NC, USA

maximum cursor excedeed...

Postby olivier.rx » Mon 15 Nov 2004 13:51

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!!!
olivier.rx
 

Re: Max Cursor Limit Exceeded

Postby Oleg » Mon 15 Nov 2004 15:30

Please check that you close your OracleDataReader objects and call Dispose for your OracleCommand.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Re: Max Cursor Limit Exceeded

Postby John » Mon 15 Nov 2004 17:57

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
 
Posts: 25
Joined: Fri 12 Nov 2004 21:18
Location: Durham, NC, USA

Re: Max Cursor Limit Exceeded

Postby John » Mon 15 Nov 2004 18:42

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
John
 
Posts: 25
Joined: Fri 12 Nov 2004 21:18
Location: Durham, NC, USA

maximum cursor excedeed...

Postby olivier.rx » Wed 01 Dec 2004 10:45

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.
olivier.rx
 

Re: maximum cursor excedeed...

Postby Oleg » Fri 03 Dec 2004 10:30

Please download OraDirect .NET 2.50 beta, it partly solve the problem.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Postby Roel Schreurs » Thu 10 Feb 2005 11:45

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.
Roel Schreurs
 

Postby Oleg » Thu 10 Feb 2005 12:26

This problem was solved in OraDirect .NET 2.50.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Thanks

Postby Guest » Thu 10 Feb 2005 13:09

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


Return to dotConnect for Oracle