If many of your queries are done over db links, you will probably encounter the ORA-02020
(too many database links in use) error. The cause for this error according to the Oracle documentation is ” The current session has exceeded the INIT.ORA open_links maximum” . The default value for this parameter is 4 but this can easily be verified by running the following query

select * from v$parameter where name ='open_links'

In plain English, this means that the current session is using more than 4 db links.

The problem is that Oracle keeps the database link open until the session is closed. This is especially problematic when working with Weblogic data sources pools for example. Since the Weblogic server keeps its session open constantly, all queries are using the same session (or sessions). This cause the number of open links to accumulates with every query from a new remote server. The final result is that when the fourth database link is used the “ora-02020-too many database links in use” is raised. The fact that many times this message is raised when running a query that uses only one database link makes it hard to debug.

According to Oracle the solution is to Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases. However, changing the open_links parameter can not be done online and require downtime. In addition, not every DBA will be happy to increase this parameter.

Using the following query to identify the number of open database links will show that rolling back or committing does not always free up the open links.

select db_link from v$dblink;

The solution is to actively close the db link after every query using the following command.

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')
This is equivalent to
execute immediate 'alter 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) loop

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

UPDATE: Apperantly, this procedure can act as a workaround for the ORA-28267 Invalid NameSpace error that could happen when your SOA suite connects to a database using JDBC and try to invoke an object that connect to a remote DB. For example, weblogic connects to a function on one DB and this function uses a db link. if you add a call to “rollback_and_close_db_links ” before returning the result, you can workaround this error.