1 2 Previous Next 17 Replies Latest reply: Nov 2, 2006 8:09 AM by Billy~Verreynne RSS

    Max open cursors exceeded

    542546
      Hi
      We are running perl script which will populate different tables in the database. In the middle of the run we got his error
      java.sql.SQLException: ORA-01000: maximum open cursors exceeded

           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
           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.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
           at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:499)
           at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:941)
           at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:531)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1036)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2904)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2945)

      and we set the open_cursors in init.ora file as 8192

      How can we avoid this problem

      Thanks & regards
        • 1. Re: Max open cursors exceeded
          Satish Kandi
          What does this return you?

          select * from v$parameter where name = 'open_cursors';

          Also, you need to review your application logic as to why so many cursors are opened in one session..
          • 2. Re: Max open cursors exceeded
            orawarebyte
            -Check is there any explicit cursor is getting opened with in any explicit cursor i mean nested explicit cursor.

            -Check yours explicit cursor is closed properly after executing the statment of
            codes.

            Khurram
            • 3. Re: Max open cursors exceeded
              orawarebyte
              duplicate..
              • 4. Re: Max open cursors exceeded
                440025
                have u restarted the database after changing the init.ora file.
                restart is required for the new effect to take place.
                • 5. Re: Max open cursors exceeded
                  Billy~Verreynne
                  Using Perl-DBI?

                  The vast majority of times, 99.9% I would say, the "maximum open cursors exceeded" error is caused by reference cursor leakage in the client (usually Java) application.

                  Ref cursors in Oracle PL/SQL are "client cursors". I.e. PL/SQL constructs the ref cursor on behalf of a client. The ref cursor handle (pointer) is passed to the client. The client fetches rows from the cursor handle.

                  Oracle has not idea when the client is indeed done with that ref cursor. That ref cursor must explicitly be closed by the client.

                  For some reason, this is usually not the case when dealing with Java-based clients that seem to forget this.

                  As the application does not close its ref cursors, the number of open cursors quickly accumulate and one runs into an ORA-01000 error.

                  An application (Oracle session) should normally one have a single cursor open. It is very seldom that an application will be processing two cursors at the same time (as this can most times be vastly better done using a server-side JOIN).
                  • 6. Re: Max open cursors exceeded
                    Billy~Verreynne
                    > have u restarted the database after changing the
                    init.ora file.
                    restart is required for the new effect to take place.

                    It is a Very Bad Idea (tm) to increase the open cursors parameter for this error. It is an attempt to fix the symptoms and not the problem.

                    It simply moves the Brick Wall a few metres further, allowing one to run even faster into it.
                    • 7. Re: Max open cursors exceeded
                      Satish Kandi
                      It simply moves the Brick Wall a few metres further, allowing one to run even faster into it.
                      Well said. :-)
                      • 8. Re: Max open cursors exceeded
                        542546
                        Hi Billy,

                        Means the client is opening the cursors and is not closing after that.
                        Does this mean the earlier opened cursors will not be used for the current application.

                        regards
                        • 9. Re: Max open cursors exceeded
                          542546
                          Hi Khurram Siddiqui,

                          How to check whether any explicit cursor is opening within other explicit cursor?

                          also how to check whether curosor is closeed or not?


                          regards
                          • 10. Re: Max open cursors exceeded
                            Billy~Verreynne
                            > Means the client is opening the cursors and is not
                            closing after that.

                            Correct.

                            > Does this mean the earlier opened cursors will not be
                            used for the current application.

                            Not really. Some o-o speak to explain a cursor. A cursor in an instantiation of a SQL in the Shared Pool. So the SQL is hard parsed. Execution plan determined. Stored in the Shared Pool.

                            A cursor is simply an "instatiation" of that SQL.

                            Typically a client should use a cursor (aka SQL statement handle in many APIs) once. E.g.

                            dbHandle = CreateDBSession( ... );
                            sqlHandle = ParseSQL( dbHandle, 'INSERT INTO FOO VALUES ( :1, :2 )' );
                            while not_some_condition{
                            .. some processing...
                            BindSQL( sqlHandle, 1, var1 );
                            BindSQL( sqlHandle, 2, var2 );
                            ExecSQL( sqlHandle );
                            }
                            CloseSQL( sqlHandle );
                            The SQL (cursor) handle is created once and re-used in this example. However the actual INSERT SQL now resides in the Shared Pool and can be re-used by any other database session without those sessions having to pay the "expensive price" for a hard parse of the INSERT statement. These sessions will use soft parses and simply re-use that Sharable SQL.

                            Cursor leakage occurs when the same variable (e.g. sqlHandle) above is re-used to create new SQL statements without closing that handle first.

                            In my experience, in Java (and to a lessor extent in C++), developers code PL/SQL procs to create the actual cursor. This is a good thing as it abstracts the SQL from the application and allows SQL tuning (and mods) without touching the client app code.

                            These SQL procs then return reference cursors. A ref cursor (within the PL/SQL context) has no local scope. It exists globally per session. Explicit PL/SQL cursors for example are auto closed when they become out-of-scope.

                            Ref cursors are 'client cursors' - cursor handles that are intended to be passed to a client process and processed by that client. Thus these cursor handles never go out-of-scope ito PL/SQL code or even client code. They exist within the session as a global session memory struct.

                            The client call (or a call to a PL/SQL proc) must be made to explicitly close the ref cursor.
                            • 11. Re: Max open cursors exceeded
                              542546
                              Hi,

                              Could you please tell how to check the following things.

                              How to check whether any explicit cursor is opening within other explicit cursor?

                              also how to check whether curosor is closed or not?


                              regards
                              • 12. Re: Max open cursors exceeded
                                orawarebyte
                                Hi,

                                Could you please tell how to check the following
                                things.
                                How to check whether any explicit cursor is opening
                                within other explicit cursor?

                                also how to check whether curosor is closed or not?


                                regards
                                There is no any tool to figure out the aforesaid staments ,you will have to check it by
                                reading yours own code or just paste the code here which will helpful to others to
                                find out whats the exact problem.

                                Khurram
                                • 13. Re: Max open cursors exceeded
                                  Billy~Verreynne
                                  What you can do on the Oracle side is identify the session that is potentially leaking cursors and the cursor statements. I've found that this information suffices to track down the problem in the client code.

                                  The following SQL looks at the virtual view called V$OPEN_CURSOR. It lists sessions that have multiple cursors opened for the same SQL statement.

                                  select
                                  c.sid,
                                  c.address,
                                  c.hash_value,
                                  COUNT(c.saddr) as "Cursor Copies"
                                  from v$open_cursor c
                                  group by
                                  c.sid,
                                  c.address,
                                  c.hash_value
                                  having
                                  COUNT(c.saddr) > 2
                                  order by
                                  3 desc
                                  Once you have the SQL/cursor address, you can find the SQL statement. The SID (Session ID) can be used to find the details of the client session in the V$SESSION table.
                                  • 14. Re: Max open cursors exceeded
                                    542546
                                    Is restarting database is required, after increasing OPEN_CURSORS in init.ora file?
                                    We are using Oracle10g. Some body told me restart is not needed.
                                    Could u please tell what exactly we have to do?
                                    1 2 Previous Next