Click here to Skip to main content
15,936,975 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 22-Jun-16 22:28pm

1 solution

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
JavaScript
DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')


Quote:
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) loop

 DBMS_SESSION.CLOSE_DATABASE_LINK (links.db_link);
 end loop;
 end;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900