1 2 Previous Next 15 Replies Latest reply on Jan 31, 2012 9:20 AM by jossy

    ORA-01000: maximum open cursors exceeded

    782742
      Hi All,
      I want to know decription of this error.
      I got following Error in my application.
      can any one tel me why cursors are created explicitly when a record is inserted into a table? what exactly happens at the back end?
      Wed Jul 27 15:32:05 IST 2011 : ORA-01000: maximum open cursors exceeded

      java.sql.SQLException: ORA-01000: maximum open cursors exceeded

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:137)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:625)
      at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
      at oracle.jdbc.driver.T4CStatement.execute_for_describe(T4CStatement.java:351)
      at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:951)
      at oracle.jdbc.driver.T4CStatement.execute_maybe_describe(T4CStatement.java:383)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1057)
      at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1218)
      at com.evermind.sql.FilterStatement.executeQuery(FilterStatement.java:157)
      at com.evermind.sql.FilterStatement.executeQuery(FilterStatement.java:157)
      at com.evermind.sql.StatementBCELProxy.executeQuery(StatementBCELProxy.java:38)
      at general.g__Inward__NewInwardDisposal._jspService(g_Inward_NewInwardDisposal.jsp:119)
      at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
      at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:347)
      at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:509)
      at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:413)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65)
      at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
      at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:649)
      at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322)
      at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:220)
      at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
      at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
      at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
      at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
      at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65)
      at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
      at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:16)
      at com.iic.common.SessionCheckForActionFilter.doFilter(SessionCheckForActionFilter.java:62)
      at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:645)
      at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322)
      at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270)
      at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
      at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:186)
      at java.lang.Thread.run(Thread.java:534)
        • 1. Re: ORA-01000: maximum open cursors exceeded
          vasanth
          Hi,

          Please refer the following link,

          http://www.orafaq.com/forum/t/59743/2/

          Thanks,
          Vasanth
          1 person found this helpful
          • 2. Re: ORA-01000: maximum open cursors exceeded
            Lubiez Jean-Valentin
            Hello,

            java.sql.SQLException: ORA-01000: maximum open cursors exceeded
            ...
            what exactly happens at the back end?
            You may check the cursors opened with the view v$open_cursor:

            http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_2083.htm#REFRN30166

            Else about the error ORA-01000 you'll have more detail below:

            http://www.error-code.org.uk/view.asp?e=ORACLE-ORA-01000

            I don't know your application but, this Note from MOS may give you some tracks:
            Investigating "ORA-01000: maximum open cursors exceeded" WebLogic Server Support Pattern [ID 1141024.1]
            Hope this help.
            Best regards,
            Jean-Valentin

            Edited by: Lubiez Jean-Valentin on Jul 27, 2011 12:51 PM
            1 person found this helpful
            • 3. Re: ORA-01000: maximum open cursors exceeded
              Billy~Verreynne
              user13355644 wrote:

              I want to know decription of this error.
              I got following Error in my application.
              can any one tel me why cursors are created explicitly when a record is inserted into a table? what exactly happens at the back end?
              Sounds like your application is leaking cursor handles.

              On the back-end - all SQL statements, every single one, is handled as a cursor. A brand new SQL statement is hard parsed and stored as a new cursor. If the client passes an SQL statement that already exists in the Shared Pool, a soft parse happens and the existing SQL cursor is re-used.

              The client gets a pointer (aka SQL statement/cursor handle) in response. The client uses this handle to interface with the cursor in server memory. Execute it. Fetch data output by it. Bind new values to it. Etc.

              The client is also expected to close that cursor handle when done. It needs to tell the server when it is done with that SQL cursor on the server side, so that the server can release the session resources it is using to service the client's cursor interface calls.

              A single session has a limit on the number of concurrent cursors that can be opened - obviously as each open cursor consumes resources on the server side.

              It seems that the error you are getting is due to your code not closing the cursor handle when it is finished with it. This means your code creates new cursors in Oracle all the time. Likely even for the very same SQL statement.

              The solution is simple. Close a cursor when your code is done with it.

              Only leave a cursor handle open when you can re-use it. For example, you parse a SQL INSERT as a cursor. Your code receives the handle to call the server and interface with that insert cursor created.

              Your code can now repeatedly bind new values to the cursor handle and execute it - thereby reusing the same cursor handle for inserting data. When done with the insert process, close the handle.

              Buggy code is likely to not close the existing insert cursor and not re-use it either. Instead, it creates a brand new duplicate insert cursor handle - for every row inserted. A sure way to run into the max allowable open cursor handles for that client session. Bad for server resources. Worse for performance. Cursor are designed as re-usable programs (like reusing the same insert cursor multiple times in a client).

              Check your OCI interface (you are likely using an abstraction layer) and make sure that you understand how to use that abstraction layer, and ensure that abstraction layer correctly use the OCI for creating re-usable cursor handles.
              • 4. Re: ORA-01000: maximum open cursors exceeded
                734879
                You didn't ask how to troubleshoot, but something like the following you should point you in a direction.
                select * from (select count(*),sql_id from gv$open_cursor group by sql_id,sid,inst_id order by 1 desc) where rownum <= 10;
                You should see a given SQL statement very close in value to your open_cursor parameter. Look in your code for where the SQL is that is returned from what is above. Check for the associated connection.createStatement, or connection.prepareCall(), etc. After that, check for the close of the same statement instance variable.

                If you use Oracle connection pooling (OracleDataSource class), keep in mind connection.close() method will not close the associated statement handles (cursors) without you closing them yourself.
                1 person found this helpful
                • 5. Re: ORA-01000: maximum open cursors exceeded
                  782742
                  Hi Billy,
                  Thank You for the response.

                  what do you mean by "Sounds like your application is leaking cursor handles".
                  Should i suggest the developers to change any code so that cursor ill be closed?
                  OR
                  how can i close the cursor @ DB end (is there ill be any script)when code is done?

                  I have changed the OPEN_CURSOR=1500 from 300 by alter command.
                  and checked ,there was no error.

                  then i restarted the database, by which it reflects the same old parameter 300.
                  Then error is raising again...
                  What could be a permanent solution for this? Shall i change OPEN_CURSOR=1500 in parameterfile?
                  or any change in code

                  Thank You



                  Rajini.V
                  • 6. Re: ORA-01000: maximum open cursors exceeded
                    Billy~Verreynne
                    user13355644 wrote:

                    what do you mean by "Sounds like your application is leaking cursor handles".
                    Should i suggest the developers to change any code so that cursor ill be closed?
                    OR
                    how can i close the cursor @ DB end (is there ill be any script)when code is done?
                    You cannot close the cursors at the db side, as that cursor state is for a specific session. That session will need to make the call to close it cursors. One session cannot close the open cursors of another session.

                    So the developers need to fix their code. It is most definitely an application problem that needs to be corrected. It will also impact application and db performance (additional soft parsing at best, possibly additional hard parsing at worse if the app code does not use bind variables).
                    I have changed the OPEN_CURSOR=1500 from 300 by alter command.
                    and checked ,there was no error.
                    Horrible idea. That is simply moving the very hard brick wall a few feet further away... and then using that extra few feet to run even faster into that wall.

                    The correct solution is for the application to be corrected. As it is currently, that application is flawed. Fact. Not speculation.
                    1 person found this helpful
                    • 7. Re: ORA-01000: maximum open cursors exceeded
                      782742
                      Hi Billy,
                      Thank You for your explanation.
                      Now i suggest the developers to correct their code.
                      I am not changing OPEN_CURSOR to 1500:)

                      Thank You for earliest response:) :)


                      Will post the description ,Once the issue gets resolved!!!!!!!!!!

                      Rajini.V
                      • 8. Re: ORA-01000: maximum open cursors exceeded
                        Billy~Verreynne
                        If your developers are using Java, then pay special attention to Steve's response above.
                        • 9. Re: ORA-01000: maximum open cursors exceeded
                          782742
                          Hi Steev,
                          Following is the Output for the query u have given.

                          COUNT(*)     SQL_ID

                          10     bkdusjx00dsmc
                          4     bkdusjx00dsmc
                          4     cqgv56fmuj63x


                          Our Developers has implemented what you have suggested, Still we are facing the same issue.
                          The problem is @ back end v have 1427 rows and some time it is working and somw time it is throwing the error.

                          Please add your valuable points.


                          Thank you

                          Rajini.V
                          • 10. Re: ORA-01000: maximum open cursors exceeded
                            Dom Brooks
                            Once you are absolutely sure that your application is not leaking cursors - and you have the information to check that above - then the likelihood is that your limit is just set too low for your application and you need to increase it in whatever method makes it permanent (i.e. using scope=spfile|both if spfile or in the param file if not using spfile).
                            1 person found this helpful
                            • 11. Re: ORA-01000: maximum open cursors exceeded
                              782742
                              Hi Dom,
                              Our application has many open connection which are not closed (Identified just 2 hours back),Now
                              we are trying to close them, we have 8000 JSP files and don't know if we have any better solution.
                              If you could check my above posts i have even tried what you have suggested.





                              Rajini.V
                              • 12. Re: ORA-01000: maximum open cursors exceeded
                                Billy~Verreynne
                                user13355644 wrote:

                                Our Developers has implemented what you have suggested, Still we are facing the same issue.
                                The problem is @ back end v have 1427 rows and some time it is working and somw time it is throwing the error.
                                I dispute that the problem is at the back-end. The problem is with the front-end or application layer that does not close cursors correctly.

                                This is the basic call sequence of a client using the OCI (<i>Oracle Call Interface</i>):
                                // initialise OCI
                                OCIInitialize() 
                                OCIEnvInit()
                                OCIHandleAlloc()  // allocate an error handle
                                
                                // make an Oracle connection
                                OCILogon()
                                
                                // create a SQL cursor
                                OCIHandleAlloc()
                                OCIStmtPrepare()
                                
                                // execute the cursor - hopefully doing some OCIBindByName()'s first
                                OCIStmtExecute()
                                
                                // close the cursor
                                OCIHandleFree()   
                                ..
                                If your application code does not do an OCIHandleFree() for that SQL cursor handle, the code is leaking handles and the code will run into ORA-1000 as a result.

                                This problem cannot be solved on the server side as the error is in the actual application code. Application code cannot call OCIHandleAlloc() to create a SQL cursor and then expect Oracle to know when the application code has finished using that SQL handle.

                                Whatever interface or abstract layer or object classes you use in your application code to use the Oracle Call Interface, this MUST follow the above OCI call sequence. This code MUST provide a means for the application to call OCIHandleFree() after the application has finished using a SQL/cursor statement handle.

                                So either your application code is using an interface layer that is poorly designed and does not support this correctly.

                                Or more likely, your application code is incorrectly using that interface layer.

                                An easy way to see cursor leakage is to look at the number of open cursor handles in a session for the very same cursor. The basic SQL query is as follows (essentially the same as what Steve posted, but for non-RAC and with additional explanation):
                                select
                                        c.sid,
                                        c.address,
                                        c.hash_value,
                                        count(*)     as "Cursor Copies"
                                from    v$open_cursor c
                                group by
                                        c.sid,
                                        c.address,
                                        c.hash_value
                                having
                                        count(*) >= 5
                                This SQL lists the SID (session identifier) of an Oracle session, together with the SQL cursor address, and number of open cursor handles in that session for that cursor, where there are (for example) 5 or more open cursor handles.

                                The SQL address columns can be used to find the actual SQL cursor (and SQL source code) in the v$sqltext virtual view.

                                The SID column can be used to find the actual Oracle session details on the v$session table.

                                Use this to identify the session. Then use the cursor SQL address to identify the actual SQL source code. As you now know both the client application (via its Oracle session details) and the SQL source, you can troubleshoot and debug that application and correct it.
                                • 13. Re: ORA-01000: maximum open cursors exceeded
                                  782742
                                  Java Code has been rectified.

                                  Thank you all for your valuable suggestions.


                                  Rajini.V
                                  • 14. Re: ORA-01000: maximum open cursors exceeded
                                    Billy~Verreynne
                                    Appreciate this feedback - we seldom get word whether or not the root cause problem was found and resolved. :-)
                                    1 2 Previous Next