?
Solved

ORA-01000: maximum open cursors exceeded

Posted on 2007-10-15
13
Medium Priority
?
1,868 Views
Last Modified: 2013-12-19
Hi, I got this when I simulated 100 concurrent user access (each user access will take more than 1 connection): ORA-01000: maximum open cursors exceeded. I think I can change the max open cursor's value to 500 through the config file (.ora). How do I check whether it had been changed correctly? Any command that I can execute? Besides this and processes variables, is there any other variable that will limit the number of connection? Please advise, thanks.
0
Comment
Question by:rospcc
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20076598
once you change the ini file, restart the oracle database, and to see the value difference before/after, run this query before/after:

select value from v$parameter where name = 'max_cursors';
0
 
LVL 48

Expert Comment

by:schwertner
ID: 20076634
I will appretiate if you share with us the Oracle version you
are speaking about.

In the Oracle versions beginning from Oracle 9i the init<SID>.ora
file is not used, it is use for some specific purposes and backward
compatibility.

So you have to make your changes in SPFILE if you are using 9i
or 10g:

SQL>ALTER SYSTEM SET open_cursors= 600 SCOPE=BOTH;

To avoid the problems with the opened cursors you have to ask
the developers to close the connections pools after they do their
task.
0
 

Author Comment

by:rospcc
ID: 20076663
Sorry, I'm using Oracle version 9i. Let me try out.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Assisted Solution

by:konektor
konektor earned 400 total points
ID: 20076891
i think ALTER SYSTEM SET open_cursors= 600 SCOPE=BOTH; should work also on 9i,
if not, use
create pfile from spfile
edit pfile, change value
create spfile from pfile
shutdown
change created spfile to by your actual spfile
staartup

but in many cases value of open_cursor is big enough. value means how many "cursors" can be opened within 1 session (not at all). oracle creates "cursor" all the time some sql statement is executed. typical processing includes: parse statement, bind variables, open, fetch, close. if the error appears in some C++ or java application, it my mean, that your applications doesn't close statements corectly - often in "catch" blocks if some error occurs.
0
 

Author Comment

by:rospcc
ID: 20082678
I simulated 100 concurrent user accessing multiple pages using MS SQL (without any delay between each transaction) and the connection used kept at 400+. Which means each concurrent access consuming about 4 connections. The default Max open_cursors provided by Oracle is only 300, I think therefore the system had issue. Is it normal for each concurrent access accessing many pages to hog about 4 connections?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20083183
>accessing multiple pages using MS SQL
how did you set that up?
note that "open cursors" is NOT the same as "connections" ... each connection can have multiple cursors...
please clarify
0
 
LVL 48

Expert Comment

by:schwertner
ID: 20083620
The issue you have is caused by the bad properties of the application you use
or by the midleware.
Check:
1. does the application closes the connections when exiting.
This is the main cause for this error. The application should explicitelly
close all opened connection.
2. does your middleware open too much connections? does it closes the connections
when the front end application quits?

You have to find the answer in this area.
Even you set the value of the opened cursors to 10000
by bad software this limit will be reached.
0
 

Author Comment

by:rospcc
ID: 20084264
public class ConnectionBean
{
  private static Connection con = null;


  public static Connection getConnection()
  {
    try
    {
            InitialContext context = new InitialContext();
            //Look up our data source
            DataSource ds = (DataSource) context.lookup("jdbc/connpool");
            //Allocate and use a connection from the pool
            con = ds.getConnection();
    }
    catch (Exception e)
    {
      System.out.println("getConnection" + e);
    }
    return con;
  }

  public synchronized static void close() throws Exception
  {
    if (con != null)
    {
          con.close();
        con = null;
    }
  }

}

public class sqlMethod {
      
      private static ResultSet rs=null;
      private static Connection con=null;
      private static Statement st=null;

      public static ResultSet rsQuery(String sql)
        {
              try
              {
                    con=ConnectionBean.getConnection();
                    st=con.createStatement();
                    rs=st.executeQuery(sql);
                    
                    
              }
              catch(Exception e)
              {
                    System.out.println("sqlMethod rsQuery is abnormal"+e);
              }
              finally
              {
                    close();
              }
              return rs;
        }

       public synchronized static void close()
        {
              try
              {
                    if (con != null) {
                          ConnectionBean.close();
                    }
              }
              catch(Exception er)
              {
                    System.out.println("con='" + con + "' close is abnormal "+er);
              }

        }
 
        public synchronized static void closeStmt()
        {
              try
              {
                    if (st != null) {
                          st.close();
                          st = null;
                    }
              }
              catch(Exception er)
              {
                    System.out.println("st='" + st + "' close is abnormal "+er);
              }

        }
}


public int getID(int iCandID) {
            int iID = 0
            ResultSet rs = null;
            try
            {
                  String query = "SELECT * FROM ID WHERE (FKID= " + iCandID + ")";
                  rs=sqlMethod.rsQuery(query);      
           
            if(rs.next()) {
                  iID = rs.getInt("PKID");
            }
            }
            catch(Exception E)
        {
            System.err.println(E);
        }
        finally
        {
              if(rs != null)
              {
                    try{
                      rs.close();
                rs = null;
                    } catch(Exception E)
                {
                          E.printStackTrace();
                }
                      
              }
              sqlMethod.closeStmt();
              sqlMethod.close();
        }

            return iID;
    }
0
 

Author Comment

by:rospcc
ID: 20084284
Do you guys do Java Programming? I just simulated 10 concurrent access to the JSP page (without any other codes, only 1 code to access the Java method getID. I believe the resultset and connection had been closed properly. I noticed that the open_cursor kept on going up.

It doesn't happen when I simulated only 1 user access. I can see that the open_cursor maintained at 1. Any idea what happened? =(
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20084309
>> I noticed that the open_cursor kept on going up
It seems ok to me since u urself said that u simulated 10 "concurrent" access to the JSP page. Do them 1 by 1 and see if they still keep incrementing.
0
 

Author Comment

by:rospcc
ID: 20084356
Our application is supposed to be able to handle 100 concurrent access. Now, it can't even handle 10 concurrent access.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 20091340
To check the opened cursors:
set trimspool on
set linesize 1000
break on report
comp sum of curs on report
select SUBSTR(User_Name,1,20) User_Name, SID, count(*) Curs from v$open_cursor group by User_Name,
SID order by User_Name, SID;
0
 
LVL 48

Accepted Solution

by:
schwertner earned 1200 total points
ID: 20091368
This is a small colection of thoughts related to this issue:

You must call the method 'close' on every statement object you create.

PreparedStatement stmt= db.prepareStatement( "select .."

ResultSet rs= stmt.executeQuery();

while (rs.next())
{
  ..
}

rs.close();

stmt.close();

If in this case any method call between 'prepareStatement' and 'close' raises an exception then the program doesn't reach the 'close' method and the statement handle is lost!




I have answered such question and this is the summary of my answers:

You have a JDBC application which runs for several hours.  It then returns the following error:
  ORA-1000 Maximum open cursors exceeded

The open_cursors parameter in init.ora is currently set to a number in excess of the maximum number of cursors you expect your application to have open at any one time.  Increasing open_cursors extends the period of time before failure, but does not resolve the issue.
Solution Description
--------------------
Typically, in Java, when an object goes out of scope, it is automatically garbage collected, but the Java specification does not place any specific requirements on the JVM regarding when (or even if) this will occur for any
particular object.  Therefore, do not rely on finalizers to close your cursors.

Explicity close all cursors ResultSet.close() and Statement.close() when you no longer need the cursor.  This ensures that the corresponding server-side cursors are closed, preventing the ORA-1000 error.

You can query the SQL_TEXT column in V$OPEN_CURSOR view from the schema to determine which cursors are not being closed.  For example:

   select sql_text from v$open_cursor;


Reference
---------

"Oracle8i JDBC Developer's Guide and Reference", Chapter 3 'Basic Features',
Section 'First Steps in JDBC'  
 
1. You should not use the finally block to reliably close cursors.
2. You should design your code to ensure that the resultSet/cursor and staements go out of scope after the close() method is invoked. This is necessary in order to execute garbage collection and reclaim the memory allocated for cursors



The problem arises often and appear to only have open cursors when using stored procedures and functions.
Try two stragies to resolve the problem:

1) Increase the value for open_cursors in our init.ora file from the default of 50 to 200

2) Have a thread running in our connectionpool class which looks for idle connections older than XX minutes, and then closes them and replaces them by new connections.

The effect of 2) should be to reduce the number of cursors open concurrently. By staggering the open and close we shouldn't put a significant overhead on Oracle or our web splication.
 
-----------
I think Connection.close() will close the resultset of the connection and will help to clear the memory as in the example below



public class JDBCLeak
{
  static
  {
      new oracle.jdbc.driver.OracleDriver();
  }

  public static void main(String args[])
  {
      while (true)
      {
          // replace url, uid, and pwd with valid data.
          Connection dbConnection =
              DriverManager.getConnection(
              url, uid, pwd);
          dbConnection.close();
          System.gc();
      }
  }
}

Sincerely

Schwertner
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question