java.sql.SQLException：侦听器拒绝连接，错误如下：ORA-12519，TNS：找不到合适的服务处理程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16230446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found
提问by user2314206
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.
我将结果集对象传递给每个线程。每个线程都连接到数据库并插入数据。直到线程 110 它工作正常。在它穿过 111 个线程后，它会抛出上述异常。
I am using oracle 11g.
我正在使用 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.
我不知道如何处理这个异常。
回答by Kai
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.
我猜数据库只是不接受来自主机的更多连接。如果我正确理解您的问题，您可能会创建 100 个线程，每个线程都在短时间内连接到数据库。也许您甚至没有正确关闭连接，或者访问持续时间太长以至于打开了大量连接。数据库有一个限制，它接受连接。
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.
您绝对应该通过一些巧妙的技术来减少连接数。也许减少并发线程的数量和/或使用连接池。
回答by Ravi Thapliyal
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:
检查进程或会话的 MAX_UTILIZATION 是否太接近 LIMIT_VALUE。如果是，您应该：
- Use DB Connection pooling to share
Connectionobjects between threads. Or, - Increase the number of processes/sessions that Oracle can service simultaneously.
- 使用数据库连接池
Connection在线程之间共享对象。或者， - 增加 Oracle 可以同时提供服务的进程/会话数。
Actually, Connection Pooling (#1) should always be done. An application cannot scale up otherwise. Check Apache Commons DBCPfor details. For #2, open a new SQL*Plus session as SYSTEM and run:
实际上，连接池（#1）应该总是被完成。否则应用程序无法扩展。查看Apache Commons DBCP了解详细信息。对于 #2，以 SYSTEM 身份打开一个新的 SQL*Plus 会话并运行：
ALTER system SET processes=<n-as-per-number-of-threads> scope=spfile;
to increase backend concurrency. Then RESTART the Database. IMPORTANT!
增加后端并发。然后重新启动数据库。重要的！
回答by Ishan Parikh
Try this solution at your end. It worked for me. Close the connection in try/catch block and just after closing the connection, write-
最后试试这个解决方案。它对我有用。在 try/catch 块中关闭连接，并在关闭连接后，写入-
Thread.sleep(1000);
In this case you can write it as-
在这种情况下，您可以将其写为-
finally {
try {
if (conn != null && !conn.isClosed())
{
conn.close();
Thread.sleep(1000);
}
}
catch (SQLException e) {
e.printStackTrace();}
}
