java.sql.SQLException:侦听器拒绝连接，并出现以下错误:ORA-12519，TNS:找不到合适的服务处理程序 [英] java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

问题描述

我正在将Resultset对象传递给每个线程.每个线程都连接到数据库并插入数据.直到线程110正常为止.越过111个线程后，它将引发以上异常.

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.

我正在使用oracle 11g.

I am using oracle 11g.

我的示例线程代码是:

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.

推荐答案

您的多线程应用程序打开了太多的Connections/Sessions.因此，侦听器会暂时删除并阻止新的连接.

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');

检查进程或会话的MAX_UTILIZATION是否太接近LIMIT_VALUE.如果是，则您应该:

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. 使用DB连接池在线程之间共享Connection对象.或者，
2. 增加Oracle可以同时服务的进程/会话的数量.

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

实际上，应始终执行连接池(#1).否则应用程序无法扩展.有关详细信息，请检查 Apache Commons DBCP .对于#2，以SYSTEM身份打开一个新的SQL * Plus会话并运行:

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!

这篇关于java.sql.SQLException:侦听器拒绝连接，并出现以下错误:ORA-12519，TNS:找不到合适的服务处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！