PROBLEM:

While querying against a database link, got below error.

select sysdate from dual@DB5
                         *
ERROR at line 1:
ORA-02020: too many database links in use

CAUSE & SOLUTION:

open_links parameter control, the number of database links each session can use without closing it.
If you access a database link in a session, then the link remains open until you close the session.

SQL> show parameter open_link

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4

Here open_links is set to 4, i.e a session can access only 4 open database links in that session.When the open db_link connection reaches the limit(open_links), it throws ORA-02020: too many database links in use.

Solution:

1. Close the open db_link connections
2. Increase the open_links parameter (bounce required)

Let’s reproduce this error.

SQL> select sysdate from dual@DB1;

SYSDATE
---------
30-JUL-17

SQL> select sysdate from dual@DB2;

SYSDATE
---------
30-JUL-17

SQL> select sysdate from dual@DB3;

SYSDATE
---------
30-JUL-17

SQL> select sysdate from dual@DB4;

SYSDATE
---------
30-JUL-17

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

Now we reached maximum open database link connections.

— View the open database link connection[Need to run this from same session ]

-- The table v$dblink populates data only for the current session, 

SQL> select db_link,logged_on,open_cursors from v$dblink;

DB_LINK          LOG OPEN_CURSORS
---------------- --- ------------
DB1              YES            0
DB2              YES            0
DB3 	         YES            0
DB4        	 YES            0

We can see there are 4 open database link transactions and it is matching the open_links parameter( i.e 4). So quick way to fix is to close these connections.

SQL>  alter session close database link  DB4;
ERROR:
ORA-02080: database link is in use

Now we are getting an ORA-02080 error. So before closing the database link, we need to either commit/rollback.

SQL> commit;

Commit complete.

SQL>  alter session close database link  DB4;

Session altered.

SQL>  select db_link,logged_on,open_cursors from v$dblink;


SQL> select db_link,logged_on,open_cursors from v$dblink;

DB_LINK          LOG OPEN_CURSORS
---------------- --- ------------
DB1              YES            0
DB2              YES            0
DB3 		 YES            0

SQL> select sysdate from dual@DB5;

SYSDATE
---------
30-JUL-17

Alternative solutions is:

Increase the open_links parameter.

alter system set open_links=8 scope=spfile;

shutdown immediate;
startup