Java - ORA-00020: maximum number of processes (x) exceeded

Hello, Today we suddenly had to deal with the below error: "javax.servlet.ServletException: TransactionServlet.doGet: Failed checking db session: javax.servlet.ServletException: java.sql.SQLException: ORA-00020: maximum number of processes (500) exceeded" After a short discussion with the DBA team it seems the application server where our application is running on was using about 350 of the available (500) connections! The rest of the connections was used by other apps. It seems that of those 350 database connection at about 120 had an idle time of >= 1 day. Can anyone tell me how I can configure our database connection in that way that idle database connection >= i day are removed? Ok, how if the connection setup. We have a DBSupport.java instance where the getConnection method is called: public OracleConnection getConnection() throws SQLException, Exception { OracleConnection conn = null; if (this.ds == null) { System.out.println("DataSource not set"); throw new Exception("DataSource not set."); } try { System.out.println("get OracleConnection"); conn = (OracleConnection)opc.getConnection(); return conn; } catch(SQLException e) { System.out.println("DataSource not set" + e.toString()); throw new SQLException("Database Problem. Detail is " + e.toString()); } } This method returns a oracle.jdbc.driver.OracleConnection object. This index.jsp file makes an instance of the DBSupport java class creating the connection based on the credentials in the web.xml: /* * Setup data source. */ dbUrl = sc.getInitParameter("dbUrl"); if (dbUrl == null || dbUrl.trim().length() == 0) throw new EdsException("Null value in JSP Processor init parameter -dbUrl-. Contact Application Server Administrator.", errGeneralFailure); dbUser = sc.getInitParameter("dbUser"); if (dbUser == null || dbUser.trim().length() == 0) throw new EdsException("Null value in JSP Processor init parameter -dbUser-. Contact Application Server Administrator.", errGeneralFailure); dbPassword = sc.getInitParameter("dbPassword"); if(dbPassword == null || dbPassword.trim().length() == 0) throw new EdsException("Null value in JSP Processor init parameter -dbPassword-. Contact Application Server Administrator.", errGeneralFailure); try { System.out.println("index.jsp: establish edsDataSource."); edsDataSource = (OracleConnectionPoolDataSource)application.getAttribute("edsDataSource"); if (edsDataSource == null) { edsDataSource = new OracleConnectionPoolDataSource(); if (edsDataSource == null) throw new EdsException("Fatal Error. Problem in setting up the Oracle Data Source. Contact Technical Support.", "General Failure"); edsDataSource.setURL(dbUrl); edsDataSource.setUser(dbUser); edsDataSource.setPassword(dbPassword); application.setAttribute("edsDataSource", edsDataSource); } System.out.println("URL:" + ((OracleConnectionPoolDataSource)application.getAttribute("edsDataSource")).getURL()); System.out.println("User:" + ((OracleConnectionPoolDataSource)application.getAttribute("edsDataSource")).getUser()); } catch (Exception e) { throw new EdsException("Fatal Error. Problem in setting up the Oracle Data Source. Contact Technical Support. Details:" + e.toString(), "General Failure"); } try { System.out.println("index.jsp: establish DBSupport."); if(edsDataSource == null) throw new Exception("null EdsDataSource"); edsDB = new DBSupport(edsDataSource); if(edsDB == null) throw new EdsException("Creation of DBSupport failed, Null value in edsDB" + edsDB, errGeneralFailure); } catch (Exception e) { throw new EdsException("Creation of DBSupport failed." + e.toString(), errGeneralFailure); } /* * Get a connection using the DBSupport instance. */ try { System.out.println("index.jsp: Get a connection using the DBSupport instance."); conn = edsDB.getConnection(); if (conn == null) throw new Exception("Null returned by getConnection"); } catch (Exception e) { System.out.println("Failed to get a connection using the DBSupport instance, details: " + e.toString()); throw new EdsException("Problem getting a database connection. Detail is :" + e.toString(), "Request failed, due to server problems"); } I know it's not the most fashioned way this code... Do you know how I can determine what the connection idle time may be and how I can improve this? As the "edsDataSource" application attribute is programmed through the entire application 600 (JSP's) I would appreciate there is a solution without needed to change the whole connection setup. Thanks in advance -WFO

Recommended
Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more