[java] ORA-12516, TNS:listener could not find available handler

My error:

java.sql.SQLException: Listener refused the connection with the following error:

ORA-12516, TNS:listener could not find available handler with matching protocol
stack
The Connection descriptor used by the client was:
//10.2.5.21:9001/XE

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:261)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:
414)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
n.java:35)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSou
rce.java:297)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
:221)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
:165)
        at utilityService.DB_util.setOracleConnectionActive(DB_util.java:99)
        at utilityService.DB_util.getRecPreparedAuthentication(DB_util.java:124)

My common db connection class:

package utilityService;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;


public class DB_util {
    String propValue = "";
    ResultSet rec = null;
    Statement stm = null;
    PreparedStatement pre_stm = null;
    CallableStatement call_stm = null;
    Connection conn1 = null;

    /**
     * Constructure to get oracle connection
     */
    public DB_util() {

        Util util=new Util();
        propValue=util.getFilePathToSave();
        //propValue = Util.propValue;// get oracle connection
        setOracleConnectionActive();
    }

    /**
     * Close all oracle connections and result sets.
     */
    public void setOracleConnectionClose() {
        try {
            if (conn1 != null || !conn1.isClosed()) {
                if (rec != null) {
                    rec.close();
                    rec = null;
                }
                if (stm != null) {
                    stm.close();
                    stm = null;
                }
                if (pre_stm != null) {
                    pre_stm.close();
                    pre_stm = null;
                }
                if (call_stm != null) {
                    call_stm.close();
                    call_stm = null;
                }
                conn1.commit();
                conn1.close();
                conn1 = null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * return a result set according to sql sent
     * 
     * @param SQL
     * @return
     */
    public ResultSet getRec(String SQL) {
        try {
            setOracleConnectionActive();
            stm = conn1.createStatement();
            rec = stm.executeQuery(SQL);

            return rec;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rec;
        }

    }

    /**
     * Activate oracle connection
     */
    private void setOracleConnectionActive() {
        try {
            if (conn1 == null || conn1.isClosed()) {
                OracleDataSource ods = new OracleDataSource();
                if (propValue != null) {
                    ods.setURL(propValue);
                }
                conn1 = ods.getConnection();
                System.out.println("DB connection CONNECTED......");
                conn1.setAutoCommit(false);
            }
        } catch (Exception ex) {
            //setOracleConnectionActive();
            ex.printStackTrace();
            System.out.println("DB connection FAILED......");
        }
    }

    /**
     * send prepared result set with user authenticate
     * 
     * @param SQL
     * @param strInputUserMobile
     * @param strInputUserName
     * @param strInputUserPassword
     * @return
     */
    public ResultSet getRecPreparedAuthentication(String SQL,
            String strInputUserMobile, String strInputUserName,
            String strInputUserPassword) {

        try {
            setOracleConnectionActive();
            pre_stm = conn1.prepareStatement(SQL);
            pre_stm.setString(1, strInputUserMobile);
            pre_stm.setString(2, strInputUserName);
            pre_stm.setString(3, strInputUserPassword);
            rec = pre_stm.executeQuery();

            return rec;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rec;
        }

    }

    /**
     * insert sql to db which is send as a sql
     * 
     * @param SQL
     * @return
     */
    public int insertSQL(String SQL) {
        int output = 0;
        try {
            setOracleConnectionActive();
            stm = conn1.createStatement();
            output = stm.executeUpdate(SQL);
            conn1.commit();
            output = 1;

        } catch (Exception ex) {
            try {
                conn1.rollback();
                output = 0;
            } catch (SQLException e) {
                e.printStackTrace();
                output = 0;
            }
            ex.printStackTrace();

        }
        return output;

    }

    /**
     * Send a callable statement according to sent sql
     * 
     * @param SQL
     * @return
     */
    public CallableStatement callableStatementSQL(String SQL) {

        int output = 0;
        try {
            setOracleConnectionActive();
            call_stm = conn1.prepareCall(SQL);

        } catch (Exception ex) {
            try {
                conn1.rollback();
                output = 0;
            } catch (SQLException e) {
                e.printStackTrace();
                output = 0;
            }
            ex.printStackTrace();

        }
        return call_stm;

    }

}

Every transaction I refer this class and do my fetching & CRUD operations. Is there any issue with my code?

This question is related to java oracle jdbc axis2

The answer is


You opened a lot of connections and that's the issue. I think in your code, you did not close the opened connection.

A database bounce could temporarily solve, but will re-appear when you do consecutive execution. Also, it should be verified the number of concurrent connections to the database. If maximum DB processes parameter has been reached this is a common symptom.

Courtesy of this thread: https://community.oracle.com/thread/362226?tstart=-1


I fixed this problem with sql command line:

connect system/<password>
alter system set processes=300 scope=spfile;
alter system set sessions=300 scope=spfile;

Restart database.


Examples related to java

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to jdbc

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' Hibernate Error executing DDL via JDBC Statement Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] MySQL JDBC Driver 5.1.33 - Time Zone Issue Spring-Boot: How do I set JDBC pool properties like maximum number of connections? Where can I download mysql jdbc jar from? Print the data in ResultSet along with column names How to set up datasource with Spring for HikariCP? java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Exception occurring. Why? java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname

Examples related to axis2

ORA-12516, TNS:listener could not find available handler The endpoint reference (EPR) for the Operation not found is Content is not allowed in Prolog SAXParserException Java Webservice Client (Best way) How do you Change a Package's Log Level using Log4j? Difference between Apache CXF and Axis