Oracle 10g Cursors Exceeded Error Message
Hi all
We used Normal Oracle 10g Standard Edition with default setting. OS is windows server 2003 32 bit. Default cursors may be say 300 - 400. We use .NET Front end and create new connection with every new user. In all there may be around 20-30 users at a time and same connection of the user is used to fetch sql statement result from Oracle Server. Default spfile??? is used as the Initialization file.
Following Syntex is used
Dim wcommandobj As New OleDb.OleDbCommand
Dim wadopter As New OleDb.OleDbDataAdapter
wcommandobj.Connection = mconn
wcommandobj.CommandText = ""
wcommandobj.CommandText = libtsql
wcommandobj.Transaction = mKcTransaction
wadopter.SelectCommand = wcommandobj
wadopter.Fill(libdataSet, kctablename)
Here in the above example mconn is the user connection which is created only once when user logs in for the 1st time. Where wadopter.Fill(libdataSet, kctablename) commands are used very frequently may be (1000 times in sigle session of 3-4 hours ) for showing Master Item /Party Names or reports etc. SQL Result is single record in 99% cases for Item /Party search etc. and say 1000 -5000 records in 1% cases for reports etc.
We feel that default Oracle Cursors should be sufficient for 30-40 users as there will be just 30-40 mconn like connections.
But still we get Cursors exceed error and then we need to shutdown Oracle and start again.
I want to understand this behaviour of Cursors requirement in Oracle and how to set right this problem of Cursors exceeded in Oracle 10g.
Regards
Suresh Bansal