ORA-02020: Too Many Database Links In Use

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