How can I close Oracle DbLinks in JDBC with XA datasources and transactions to avoid ORA-02020 errors?

Question

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?

Answer 1

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.

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_LINKS parameter 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.

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:

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];