Microsoft sqlserver链接服务器到ORACLE数据库，数据库链接到另一个ORACLE数据库 [英] Microsoft sqlserver linked server to ORACLE database with database link to another ORACLE database

问题描述

大家好，



我遇到了连接ORACLE12数据库的Microsoft SQL Server 2014（后来称为（S1））的问题（稍后调用（O1））通过链接服务器。此ORACLE数据库还使用数据库链接连接到另一个ORACLE12数据库（稍后称为（O2））。 O1数据库有一个包函数，S1每次在单独的事务中使用afaik MSDTC（这可以按预期工作）调用。现在我试图通过O2数据库中的数据库链接从O1数据库调用另一个函数。这有效（使用标准的ORACLE配置）恰好4次，并且在第5次失败时出现ORACLE错误ORA-02020：

使用的数据库链接太多。几分钟后，这再次起作用（正好4次失败，第5次，依此类推）。如果我不通过数据库链接从O1调用O2上的函数，那么从S1到O1的调用就像一个魅力。 O1和O2函数都不进行任何提交或回滚，并且对这些函数的访问非常有限，因为它们是客户特定的。



有没有人有这方面的经验行为和任何想法如何解决这个问题？



任何帮助将不胜感激。



我尝试了什么：



我已经修改了ORACLE参数OPEN_LINKS，但唯一的结果是如果我将参数设置为10，它可以工作10次。如上所述，从S1到O1的调用按预期工作，但一旦涉及到O2，它就会在OPEN_LINKS次之后失败。

Hello all,

I'm experiencing a problem with Microsoft SQL Server 2014 (later on called (S1)) connected ot an ORACLE12 database (later on called (O1)) via a linked server. This ORACLE database is also connected to another ORACLE12 database (later on called (O2)) using a database link. O1 database has a package function which is called by S1 using afaik MSDTC (this works as expected) each time in a seperate transaction. Now I'm trying to call another function from O1 database via the database link in O2 database. This works (with standard ORACLE configuration) exactly 4 times and fails the 5th time with ORACLE error "ORA-02020:
too many database links in use". After some minutes, this works again (for exactly 4 times failing the 5th time and so on). If I do not call the function on O2 from O1 via the database link, it the call from S1 to O1 works like a charm. Both O1 and O2 functions don't do any commit or rollback and there is very limited access to these functions as they are customer specific.

Has anyone any experience with this behaviour and any idea how come around this problem?

Any help would be appreciated.

What I have tried:

I already modified ORACLE parameter OPEN_LINKS but the only result was that it works 10 times if i set the parameter to 10. As described, the call from S1 to O1 works as expected, but as soon as there is O2 also involved, it fails after OPEN_LINKS times.

推荐答案

这是一个已知问题与Oracle。要克服它，您需要在每次查询后显式关闭连接（因为Oracle将保持连接打开直到会话结束）。



来自这里 [ ^ ]



使用

It's a known problem with Oracle. To overcome it you need to explicitly close the connection after each query (as Oracle will leave the connection open until the session ends).

More detail and instructions from here[^]

Use

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')

引用：

但是，更改现有代码并不总是可行的。可能的解决方案可能是创建一个过程，该过程将关闭所有打开的数据库链接，并在使用数据库链接的查询之后或之前运行它。

However, it is not always feasible to change existing code. A possible solution might be to create a procedure that will close all open db links and run it after or before queries that uses database links.

create or replace procedure rollback_and_close_db_links
 authid current_user is
 begin
 rollback;
 for links in (select db_link from v

dblink）循环

DBMS_SESSION.CLOSE_DATABASE_LINK（links.db_link）;
结束循环;
end;

dblink) loop DBMS_SESSION.CLOSE_DATABASE_LINK (links.db_link); end loop; end;

这篇关于Microsoft sqlserver链接服务器到ORACLE数据库，数据库链接到另一个ORACLE数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！