Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1456
  • Last Modified:

Failed to obtain/create connection. Reason : Connection could not be allocated because: ORA-00020: maximum number of processes (150) exceeded

Hi, my JSP application which is connecting to Oracle is facing the issue below:
Failed to obtain/create connection. Reason : Connection could not be allocated because: ORA-00020: maximum number of processes (150) exceeded.

We actually close all the statements and connection used. Although not sure whether the system will close the result set in the event of exception. Does closing statements and connections automatically closed the resultset?

I attached snippet of our function in Java as follows. Please advise, thanks.

    public Vector getAllData()
    {
          voData vo = new voData();
          Vector v = new Vector();
       
        String querySql = "SELECT * FROM TBLDATA";
                  
        try
        {            
              ResultSet rs=sqlMethod.rsQuery(querySql);
       
            while(rs.next())
            {
                  vo = new voData();
                  vo.setDataID(rs.getString("DataID"));
                  v.add(vo);
            }
           
            rs.close();
            rs = null;
     
        }
        catch(Exception E)
        {
            System.err.println(E);
        }
        finally
        {
              
              sqlMethod.close();
              sqlMethod.closeStmt();
        }
          
        return v;
    }
0
rospcc
Asked:
rospcc
  • 6
  • 4
2 Solutions
 
objectsCommented:
> Does closing statements and connections automatically closed the resultset?

yes

where does the actual connection get closed?
0
 
rospccAuthor Commented:
 public static void close()
  {
      try
      {
            if (con != null)
                  ConnectionBean.close();
            
      }
      catch(Exception er)
      {
            System.out.println("con='" + con + "' close is abnormal "+er);
      }

  }



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

  }


Since we're using Connection pooling, ConnectionBean.close() will eventually execute the codes below.
It does not actually close the connection. It will be passed back to resource pool.

public synchronized void freeConnection(Connection con)
{
    freeConnections.addElement(con);
    checkedOut--;
    notifyAll();
}
0
 
rospccAuthor Commented:
If the connection being passed back to pool and we actually only closed the Statement, does it mean that the resultset will still remain open if there's exception?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
objectsCommented:
no, closing statement will close result set

How big is your pool? <150?
0
 
objectsCommented:
and is anything else using database?

check the process list in oracle to see what processes are running
0
 
rospccAuthor Commented:
The connection pool size from Java is 300. We didn't really set the Oracle pool. I supposed it's still using the default, which is 150.

>>and is anything else using database?
>>check the process list in oracle to see what processes are running
### May I know what other processes could possibly be running? We just installed and setup the database to use it. How do we view the processes in Oracle?
0
 
objectsCommented:
> The connection pool size from Java is 300. We didn't really set the Oracle pool. I supposed it's still using the default, which is 150.

That sounds like a recipe for distaster :)

> May I know what other processes could possibly be running?

other applications you have that would be accessing the database, its oracle running out of processes, not your app.

> How do we view the processes in Oracle?

dunno, i'm a mysql man. Will see if I can find out


0
 
objectsCommented:
0
 
Gibu GeorgeCTOCommented:
Hello rospcc,

SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

execute the above query when the error occurs and find which user(s) is(are) consuming so many connections.

Regards,

gibu_george
0
 
rospccAuthor Commented:
We're given dedicated 100 Oracle access. Is 100 access normally sufficient?
0
 
objectsCommented:
its really application dependent, though i'm not a dba
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now