-
We're running a vb application on oracle804./os nt
I keep hearing from the endusers getting the following errors despite hardly opening to many cursors....
-- -- -- -- # -2147467259
Source: Microsoft OLE DB Provider for Oracle
Native error: 0
SQL Error:
Description:
Unspecified error
ORIGINAL ERROR:
-2147467259 ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
default cursor value is 50, would increasing this value solve the problem.
also the error 00604.
would that go as well....
The end user has to log off form the application and start again, and after some time he is encountered with the same erors?
Any tips/Advise????
-
in your init.ora change OPEN_CURSORS=50 to OPEN_CURSOR=250
shutdown your database and startup and you will be fine.
-
In my experience, the problem usually is somebody that opens a cursor and then doesn't close it. After a while, the number of OPEN_CURSORS is exhaused for the session.
-
JEFF,MIKE,
OK WILL DO!
IS THERE ALSO A WAY TO CHECK OPEN CURSOR STATUS IN THE DATABASE FROM TIME TO TIME.
AS THE END USER HAS NO KNOWLEDGE OF HOW MANY CURSORS ARE BEING OPENED!!!!
THANX
-
Code:
SELECT
s.sid,
s.username,
s.program,
count(oc.sid) open_cursors
FROM
v$session s,
v$open_cursor oc
WHERE
oc.sid = s.sid
GROUP BY
s.sid,
s.username,
s.program
ORDER BY
open_cursors desc
-
If you're certain that your application is not intending to have that many cursors open in the database, then increasing the OPEN_CURSORS setting is really only masking the problem. If your application is not closing the cursors, setting the value to 250 or some other larger number will reduce the chances that the exceeded open cursors error will be seen by the user, but they will see it again at some point. Not to mention the additional server resources being dedicated to all of these extraneous open cursors.
As Jeff said, the problem is that people who are opening cursors are not closing them. If your VB code is calling the Recordset.Open method, then you need to close the recordset's cursor. If you are going to reuse the Recordset object itself at some future point, then just call Recordset.Close. If you're done with the recordset entirely, then set the recordset object = Nothing to release all references to it.
If you are getting a recordset by calling a stored procedure with a REF CURSOR output parameter, it's still the responsibility of your application to close the recordset.
If your application has processing that may require there to be a large number of open recordsets, then increasing the OPEN_CURSORS parameter is fine.
HTH,
Heath
-
Ok Heath!
Thanx much!
Would or can there be a procedure build in order to track the max open cursors activity(such as a lengthy detailed transactions so i can come to conclude where and when is it causing the 01000 and 00604 errors.
How is the 01000 error related to the 00604 error?
