DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: open cursors exceeded

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Exclamation

    Hi,

    I have encountered the error ora-01000 maximum open cursors exceeded. Currently, the value for open_cursor = 300 but when i checked the number of open cursors from v$open_cursor, there are over a thousand of them!

    I think it has got to do with the application level. When using EXEC SQL SELECT.. INTO .... in a script, will it caused the cursor to be opened and not closed after execution? Those statements are being used in a perl script and there will always be a sthm->finish() after it.... so what could be the cause of all the open cursor?? :(

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    There is a problem with your application code.
    You must explicitly close cursors when using sql statements in pl/sql blocks.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Remember open cursors is at the session level.
    Each session can have 300 open cursors.
    This will explain why you have over a thousand open cursors in v$open_cursor.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    May 2001
    Location
    BANGALORE
    Posts
    5
    hi

    I think it is due to problem with the Application code (i.e PL/SQL ) or some sql startment.This problem is due to not closing the opened cursor...
    so try walk through the code and check any where u not closing the cursor..

    if u r using any pro*c or pro*cobol programs also this type of problem may come due to not closing the cursors..


    bye
    Rama Aluri

  5. #5
    Join Date
    Jan 2000
    Posts
    387

    Unhappy

    Hi,

    Does anyone has any idea what is the correct way of closing the cursors in a perl script? Does using the statement EXEC SQL caused an open cursor? I understand that it is mainly due to application, but i am not too sure why? Simply query like "select sysdate from dual" can be be inside the list of open cursor and do not get closed too.

    Thanks.

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    On our test database, we found that exceptions in Java code will cause open cursors to persist.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    Jan 2000
    Posts
    387
    Thanks dknight. I know that by using graphics.dispose() will be able to close the cursors. But I am not using any Java objects!! So i do not think it should be the cause. The cause should comes from the SQL statement in the perl script, but I do not know why........ :( any ideas?

  8. #8
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Does your Perl script complete successfully?

    Are you performing select, update, insert, and/or delete in your SQL?
    David Knight
    OCP DBA 8i, 9i, 10g

  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What kind of operation environment it is in? To me 300 seems to be quiet low...

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Jan 2000
    Posts
    387
    The perl scripts are running in a Linux platform while the database is in a HP-UX box. How to determine if the perl scripts have executed completely? Sometimes the page that executes the SQL can load and sometimes it can't, so I do not know what could be wrong with the SQL. BUt how can a simple statement like "select sysdate from dual" can also be opened for so long?? Any known cause?

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