ORA-01000: maximum open cursors exceeded

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.

Sorry, I'm using Oracle version 9i. Let me try out.

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?

>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

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.

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;
    }

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? =(

>> 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.

Our application is supposed to be able to handle 100 concurrent access. Now, it can't even handle 10 concurrent access.

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;