Menu
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?
864
ORA-12516:TNS:listener could not find available handler with matching protocol stack. Cause: PMON update listener with information about instance such as load and dispatcher information. PROCESS parameter determines the maximum load for dedicated connection in database.
To resolve this problem, use the listener. ora parameter USE_SID_AS_SERVICE_listener_name. This parameter will tell the listener to use the SID in the connect descriptor as a service name and allow you to connect the client to this particular database.
In this case "could not find available handler" means you have reached your maximum concurrent users. However, you could increase your maximum through connection tuning. In particular, the "multi-threaded server" connection option could be helpful here. Connection tuning is a highly technical subject.
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
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
