oracle ORA-00020: maximum number of processes (150) exceeded
Dear all how can i fix this problem when i had one connection pool with orcale by jdbc and the connection size is only 10 ,
and i donot know why that's easy to by a
maximum number of processes (150) exceeded
package OConn;
import java.io.*;
import java.sql.*;
import java.util.Vector;
import Unit.Message;
publicclass PoolBean{
private String driver ="";
private String url ="";
privateint size = 0;
private String username ="";
private String password ="";
public ConnBean connBean =null;
private Vector pool =null;
private String filename =null;
static Vector v_property =new Vector();
static Message msg =new Message();
public PoolBean(){
super();
this.filename = msg.profile;
}//Constructor
publicvoid reproperty()throws Exception{
String inputline =null;
String sizes;
try{
BufferedReader is =new BufferedReader(
new FileReader(filename));
while((inputline = is.readLine()) !=null){
String strs[] = inputline.split("=");
for(int j=0; j < strs.length; j++){
if(j == 1){
v_property.addElement(strs[j].trim().toString());
}//end if
}//end for
}//end while
is.close();
for(int i = 0; i < v_property.size(); i ++ ){
if(i == 0){
url = (String) v_property.elementAt(0);
}elseif(i == 1){
username = (String) v_property.elementAt(1);
}elseif(i == 2){
password = (String) v_property.elementAt(2);
}else{
sizes = (String) v_property.elementAt(3);
size= Integer.parseInt(sizes);
}//end if
}//end for
}catch(IOException e){
msg.log(e.toString());
e.printStackTrace();
}
}//end reproperty
publicvoid setDriver(String d){
if (d !=null) driver = d;
}//setDriver
public String getDriver(){
try{
DriverManager.registerDriver(new oracle.jdbc.driver.
OracleDriver());
}catch (SQLException ex){
}
return driver;
}//getDriver
publicvoid setURL(String u){
if (u !=null) url = u;
}//setURL
public String getURL(){
return url;
}//getURL
publicvoid setSize(int s){
if (s > 1) size = s;
}//setSize
publicint getSize(){
return size;
}//getSize
publicvoid setUsername(String un){
if (un !=null) username = un;
}//set/Username
public String getUsername(){
return username;
}//getUsername
publicvoid setPassowrd(String pw){
if (pw !=null) password = pw;
}//set/Password
public String getPassowrd(){
return password;
}//getPasswor
publicvoid setConnBean(ConnBean cb){
if (cb !=null) connBean = cb;
}//set/ConnBean
public ConnBean getConnBean()throws Exception{
Connection conn = getConnection();
ConnBean cb =new ConnBean(conn);
cb.setInuse(true);
return cb;
}//getConnBean
private Connection createConnection()throws Exception{
Connection conn =null;
conn = DriverManager.getConnection(url, username, password);
return conn;
}//createConnection
publicsynchronizedvoid initializePool()throws Exception{
// 設定參數
reproperty();
setSize(10);//預設5個connection
// 設定參數
if (driver ==null)thrownew Exception("No driver name provided!");
if (url ==null)thrownew Exception("No URL name provided!");
if (size < 1)thrownew Exception("Connection pool size is less than 1!");
try{
// oracle jdbc driver
DriverManager.registerDriver(new oracle.jdbc.driver.
OracleDriver());
DriverManager.setLoginTimeout(120);
for (int i = 0; i < size; i++){
Connection conn = createConnection();
if (conn !=null){
ConnBean connBean =new ConnBean(conn);
addConnection(connBean);
}
}//for
}catch (Exception e){
msg.log(e.getMessage());
System.err.println(e.getMessage());
thrownew Exception(e.getMessage());
}//try
}//initializePool
privatevoid addConnection(ConnBean connBean){
if (pool ==null) pool =new Vector(size);
pool.addElement(connBean);
}//addConnection
publicsynchronizedvoid releaseConnection(Connection conn){
for (int i = 0; i < pool.size(); i++){
ConnBean connBean = (ConnBean)pool.elementAt(i);
if (connBean.getConnection() == conn){
System.out.println("Relaease connection number: " + i);
connBean.setInuse(false);
break;
}//if
}//for
}//releaseConnection
publicsynchronized Connection getConnection()throws Exception{
Connection conn;
ConnBean connBean =null;
for(int i = 0; i < pool.size(); i++){
connBean = (ConnBean)pool.elementAt(i);
if (connBean.getInuse() ==false){
connBean.setInuse(true);
conn = connBean.getConnection();
return conn;
}//if
}//for
try{
conn = createConnection();
connBean.setInuse(true);
pool.addElement(connBean);
}catch(Exception e){
msg.log(e.getMessage());
System.err.println(e.getMessage());
thrownew Exception(e.getMessage());
}//try
return connBean.getConnection();
}//getConnection
// if 使用者not access pool please emptypool
publicsynchronizedvoid emptyPool(){
for (int i = 0 ; i < pool.size(); i++){
System.out.println("Close JDBC connection number: " + i);
ConnBean connBean = (ConnBean)pool.elementAt(i);
if (connBean.getInuse() ==false){
connBean.close();
}else{
try{
java.lang.Thread.sleep(20000);
connBean.close();
}catch (InterruptedException ie){
try{
msg.log(ie.
getMessage());
}catch (IOException ex){
}
System.err.println(ie.getMessage());
}//try
}//if
}//for
}//emptyPool
}//PoolBean
the jsp code
<%@ page contentType="text/html; charset=big5" language="java" import="java.sql.*" %>
<!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Security Check</title>
</head>
<jsp:useBean id="poolBean" scope="session" class="OConn.PoolBean" />
<jsp:useBean id ="mycount" scope="session" class="Unit.Counter" />
<%
String Name = request.getParameter("Name");
String Password= request.getParameter("Password");
String check= request.getParameter("check");
String itemlist =null;
Connection conn =null;
int userlist = 0;
int usecount = 0;
usecount = mycount.getCount();
/*========database check=========*/
try{
if(usecount == 0){
poolBean.initializePool();
}else{
poolBean.initializePool();
}
conn = poolBean.getConnection();
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from USERACOUNT");
while(rs.next()){
//System.out.println(rs.getString("username"));
//System.out.println(rs.getString("USAGE"));
if(Name.equals(rs.getString("username")) && Password.equals(rs.getString("userpass")) ){
userlist = 1;
itemlist = rs.getString("USAGE");
}//end if
}//end while
if(Name.equals("") || Password.equals("")){
System.out.println("Name and password == null");
rs.close();
statement.close();
poolBean.releaseConnection(conn);
conn.close();
%>
<jsp:forward page="error.jsp"/>
<%
}elseif(Name.equals("admin") && Password.equals("admin") && check.equals("M")){
itemlist ="帳號管理,新聞中心,頁面管理,商品管理,B2C管理,B2B管理,客戶服務";
session.setAttribute("Name", Name);
session.setAttribute("itemlist", itemlist);
rs.close();
statement.close();
poolBean.releaseConnection(conn);
%>
<jsp:forward page="welcome.jsp"/>
<%
}elseif(userlist == 1){
session.setAttribute("Name", Name);
session.setAttribute("itemlist", itemlist);
rs.close();
statement.close();
poolBean.releaseConnection(conn);
%>
<jsp:forward page="welcome.jsp" />
<%
}else{
%>
<jsp:forward page="error.jsp"/>
<%
System.out.println("error on loing ");
}//end if
rs.close();
statement.close();
poolBean.releaseConnection(conn);
}catch(SQLException e){
System.out.println(e.getMessage());
%>
<jsp:forward page="error.jsp"/>
<%
}//end try/catch
%>
<body>
</body>
</html>
