Maximum Open Cursors Exceeded

posted March 9th 2005 at 1315 EST in All, ColdFusion, Oracle

Sometimes on a ColdFusion page which does heavy db lifting (ie: a huge number of inserts) even though the queries are auto-commited, you will run out of cursors on the database. This seems to happen mainly in my migration scripts, and nightly update scripts, but could easily happen to anyone.

Here is the error:

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01000: maximum open cursors exceeded

One solution which a number of people have offered up is to up the CURSORS number in the oracle config (go get your dba). This however isn’t always practical, especially because you have to kick the database to get that setting to take.

The problem occurs when your max-pooled statements setting on the datasource is higher than the CURSORS setting in the oracle database. By default in ColdFusion this is 1000 statements. In my experience the normal setting in Oracle 8.1.6 is 150 or 300. Big difference between the two.

ColdFusion will ‘pool’ a query when you use cfqueryparam, so that when the paramater changes (and the rest of the query stays the same) it executes faster. To ‘pool’ a statement though, it must keep a cursor open on the database.

Simply lowering the setting in the administrator so your max-pooled statements is lower than your database cursors, or removing cfqueryparam from your queries is sufficient. A setting of 100 for max-pooled statements works for me.

To check how many cursors are in use run this sql statement:

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid

10 Responses

  1. #1 Ashley
    4 years, 2 months ago

    .i appreciate your work but this sql statement did not work ..cud u send some description regarding this and a more sharper solution.Thanks

  2. #2 Arkhron
    4 years ago

    I use Oracle 9i. The statement works fine. In 9i the default cursor setting is 1000. I prefer do not touch the cursors number in the Oracle config. Just close every reader that you send with a statement when you use it.

  3. #3 John
    3 years, 12 months ago

    This is exactly the info I needed! Thanks for sharing.

  4. #4 gordon
    3 years, 11 months ago

    I have had similar problems using OCI and pooled sessions. I found that using OCIStmtPrepare there is no way to release the statement and after doing more than max open cursor queries I got the dreaded ORA-1000 error. This was solved by using OCIStmtPrepare2 and then calling OCIStmtRelease each time. (OCIStmtRelease only works with OCIStmtPrepare2.) I believe there are silimar calls in other languages.

    I never tried it, but I also think that if you set your Session Cashed Cursors parameter in the Oracle ini file to less than the Open Cursors value the cashed cursors will start being closed at that level and you will never reach the Open Cursor limit. If this works, let us all know.

  5. #5 Jayant
    3 years, 11 months ago

    How to Close Open Cursor.

  6. #6 ashish
    3 years, 10 months ago

    I am closing cursor(conn.close()),resultset(rs.close()) and statement(stmt.close()).but still it is not getting closed.

    after the block where i am closing all this,checking whether connection object is null or not. still its value is not set to null.

    Please suggest me the solution.

  7. #7 Vikram
    3 years, 8 months ago

    this is good enough to know the open cursors.Now I want to know is there any way by using which we can close all open cursors at a time?

  8. #8 Jack
    3 years, 6 months ago

    I was recently frustrated because we had an issue where max pooled statements on CF was set at 998 and oracle’s max open cursors was set to 1000. Testing had shown that 999 or 1000 would have continued to produce the error, so I assumed it was just a double fencepost error, but how CF counts its pooled statements is not that simple. I guess we just have to be sure that they are not very close at all. I have no idea what the real margin of error is, but I would suspect it has to do with the highest number of poolable statements in a single template. my 998 vs. 1000 tests were using a pretty small template; it may have only had one or two queries in it.

  9. #9 Alexandre
    2 years, 5 months ago

    Like Arkhron said, I close all my reader opened and the probleme stop comming. I search lot to found what is left open. Thanks !

  10. #10 Dreck
    1 year, 9 months ago

    Alles Dreck