DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: oRA ERRORS

  1. #1
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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????
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  2. #2
    Join Date
    Aug 2001
    Posts
    390
    in your init.ora change OPEN_CURSORS=50 to OPEN_CURSOR=250

    shutdown your database and startup and you will be fine.


  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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 Hunter

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    Jeff Hunter

  6. #6
    Join Date
    Apr 2001
    Posts
    118
    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

  7. #7
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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?
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width