如何使用 XA 数据源和事务关闭 JDBC 中的 Oracle DbLinks 以避免 ORA-02020 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5088934/
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
How can I close Oracle DbLinks in JDBC with XA datasources and transactions to avoid ORA-02020 errors?
提问by aro_biz
I have a JDBC-based application which uses XA datasources and transactions which span multiple connections, connected to an Oracle database. The app sometimes needs to make some queries using join with a table from another (Oracle) server using a shared DbLink. The request works if I don't do it too often, but after 4 or 5 requests in rapid succession I get an error (ORA-02020 - too many links in use). I did some research, and the suggested remedy is to call "ALTER SESSION CLOSE DATABASE LINK ". If I call this request after the query that joins the DbLnk table, I get the error ORA-2080 (link is in use). If I call it before the query, I get ORA-2081 (link closed). Does this call do any good at all? The JDBC connection is closed long before the transaction commit (which is managed either by servlet or by EJB container, depending on the circumstances). I get the impression that when the connection closes, Oracle marks the link as closed, but it takes a minute or two for it to return to the pool of available links. I understand I could enlarge the pool of links (using the open_links property in the config file), but that won't guarantee that I won't have the same problem under a heavier load. Is there something I can do differently to get the dblinks to close more rapidly?
我有一个基于 JDBC 的应用程序，它使用跨多个连接的 XA 数据源和事务，连接到 Oracle 数据库。应用程序有时需要使用共享 DbLink 与来自另一个 (Oracle) 服务器的表进行连接来进行一些查询。如果我不经常执行该请求，则该请求有效，但是在快速连续请求 4 或 5 个后，我收到错误消息 (ORA-02020 - 使用的链接过多)。我做了一些研究，建议的补救措施是调用“ALTER SESSION CLOSE DATABASE LINK”。如果我在加入 DbLnk 表的查询之后调用此请求，则会收到错误 ORA-2080（链接正在使用）。如果我在查询之前调用它，我会得到 ORA-2081（链接已关闭）。这个电话有什么好处吗？JDBC 连接在事务提交（由 servlet 或 EJB 容器管理，视情况而定）。我的印象是，当连接关闭时，Oracle 会将链接标记为已关闭，但它需要一两分钟才能返回可用链接池。我知道我可以扩大链接池（使用配置文件中的 open_links 属性），但这并不能保证在更重的负载下我不会遇到同样的问题。有什么我可以做的不同的事情来让 dblinks 更快地关闭吗？
回答by Jon Heller
Any distributed SQL, even a select, will open a transaction that must be closed before you can close the database link. You need to either rollback or commit before you call ALTER SESSION CLOSE DATABASE LINK.
任何分布式 SQL，甚至是选择，都会打开一个事务，在关闭数据库链接之前必须关闭该事务。在调用 ALTER SESSION CLOSE DATABASE LINK 之前，您需要回滚或提交。
But it sounds like you've already got something else handling your transactions. If it's not possible to manually rollback or commit, you should try to increase the number of open links. The OPEN_LINKSparameter is the maximum number of links per session. The number of links you need isn't really dependent on the load, it should be based on the maximum number of distinct remote databases.
但听起来您已经有了其他东西来处理您的交易。如果无法手动回滚或提交，则应尝试增加打开链接的数量。该OPEN_LINKS参数是每个会话的最大链接数。您需要的链接数量并不真正取决于负载，它应该基于不同远程数据库的最大数量。
Edit:
编辑：
The situation you describe in your comment shouldn't happen. I don't understand enough about your system to know what's really happening with the transactions. Anyway, if you can't figure out exactly what the system is doing maybe you can replace "alter session close database link" with a procedure like this:
您在评论中描述的情况不应该发生。我对您的系统了解不够，无法了解交易的真正情况。无论如何，如果你不能确切地弄清楚系统在做什么，也许你可以用这样的过程替换“alter session close database link”：
create or replace procedure rollback_and_close_db_links authid current_user is
begin
rollback;
for links in (select db_link from v$dblink) loop
execute immediate 'alter session close database link '||links.db_link;
end loop;
end;
/
You'll probably need this grant:
您可能需要这笔赠款：
grant select on v_$dblink to [relevant user];
