ORA-2020 – Too many database links in use

This is another one of those seldom-seen errors that you may encounter if you are in this business long enough. I was testing some database links for a database upgrade and saw this error during the test. A little background first. The database to be upgraded was Oracle version 8.1.6 (pause for dramatic affect) and the project manager wanted to upgrade it to the latest 12c version as there was little in the way of complicated data. I reverse-engineered the database links and placed them in a test 12c database to see if they could still see their targets. I did a basic ‘select * from dual@link’ for each. I saw the ORA- error during the sequential execution of these statements:

SQL> select * from dual@link1
*
SQL>
D
-
X

SQL> select * from dual@link2
*
ERROR at line 1:
ORA-02020: too many database links in use

SQL> select * from dual@link3
*
ERROR at line 1:
ORA-02020: too many database links in use

The problem is that Oracle keeps each link called open until the session that called it ends. The number of links a single session can have open before this error is seen is directly related to the init parameter ‘open_links’, which I then checked by querying v$parameter:

SQL> select value

2 from v$parameter

3 where name = 'open_links';

VALUE

--------------------------------------------------------------------------------

4

This explained why I started getting the error after querying four database links. What can I do if I need to test more than four links at a time? Log out and in again after every four links? That could get a bit tedious. Fortunately, there a couple of workarounds. The simplest one is to perform a commit after each link or every four links. This works for my simple testing, but a more programmatic approach would be to use this code:

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')

You can even encapsulate this command in a loop when selecting active links from v$dblink to close all existing open links in a manner similar to this:

create or replace procedure close_db_links
  authid current_user is
begin
  for lnk in (select db_link from v$dblink) 
  loop
    dbms_session.close_database_link(lnk.db_link);
  end loop;
end;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s