java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

Question

I am passing Resultset object to each thread. Each thread is connecting to the database and inserting data. Untill thread 110 it is working fine. After it crosses 111 thread it throws the above exception.

I am using oracle 11g.

My sample Thread code is:

class MyThreadClass implements Runnable 

{

    public Connection connection;

    public Statement statement2;

    public ResultSet rs2;    

    public String cookie;


    public MyThreadClass(ResultSet rs1)  
    {
      rs2=rs1;
    }

    public void run() 
    {    
       try
       {                    
            cookie=rs2.getString("COOKIE");
            driver = "oracle.jdbc.driver.OracleDriver";
            url    = "jdbc:oracle:thin:@127.0.0.1:1521:xx";
            /* connection

                statement2.executeUpdate("INSERT INTO visit_header  VALUES ('"+cookie+"')");

       }

I am not getting how to handle this exception.

Answer 1

Your multi-threaded application is opening too many Connections/Sessions. Hence, the listener is dropping and blocking new connections for a while.

Check your DB resource usage first:

SELECT * FROM v$resource_limit WHERE resource_name IN ('processes','sessions');

Check to see if your MAX_UTILIZATION for either your Processes or Sessions is getting too close to the LIMIT_VALUE. If yes, you should either:

1. Use DB Connection pooling to share Connection objects between threads. Or,
2. Increase the number of processes/sessions that Oracle can service simultaneously.

Actually, Connection Pooling (#1) should always be done. An application cannot scale up otherwise. Check Apache Commons DBCP for details. For #2, open a new SQL*Plus session as SYSTEM and run:

ALTER system SET processes=<n-as-per-number-of-threads> scope=spfile;

to increase backend concurrency. Then RESTART the Database. IMPORTANT!

Answer 2

I guess the database just don't accept more connections from your host. If I understand your question right you are making maybe 100 threads which each connects to the database in short time. Maybe you don't even close the connection correctly, or the accesses are lasting so long that a huge amount of connections are opened. The database have a limit to which it accepts connections.

You should definitely reduce the number of connections by some clever technique. Maybe reduce the number of concurrent threads and/or use a connection pool.