Skip to content
Home » Oracle » How to Resolve ORA-02020: too many database links in use

How to Resolve ORA-02020: too many database links in use


ORA-02020 means that the database limits the number of open database links on every session. You can widen the restrictions for later use. For example, after opening 4 db links, we failed to open the 5th db link.

SQL> select sysdate from dual@dblink01;

2017-10-10 19:58:16

SQL> select sysdate from dual@dblink02;

2017-10-10 19:58:22

SQL> select sysdate from dual@dblink03;

2017-10-10 19:58:33

SQL> select sysdate from dual@dblink04;

2017-10-10 19:58:39
SQL> select sysdate from dual@dblink05;
select sysdate from dual@dblink05
ERROR at line 1:
ORA-02020: too many database links in use


This is because both server parameter OPEN_LINKS and OPEN_LINKS_PER_INSTANCE are too small.

SQL> show parameter open_links

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

As we can see, OPEN_LINKS and OPEN_LINKS_PER_INSTANCE are now 4, which is the default value, which may be too small for a production database.


1. Release Some DB Links

The first solution is to close some of the db links to release quota of open links before opening other db link.

SQL> alter session close database link dblink04;

Session altered.


SQL> exec dbms_session.close_database_link ('DBLINK04');

PL/SQL procedure successfully completed.

Usually, we can't close any db link at this moment because of some ORA error. We need more steps to resolve ORA-02080: database link is in use.


The second solution is to raise the number of open db links.

SQL> alter system set open_links=20 scope=spfile sid='*';

System altered.

SQL> alter system set open_links_per_instance=20 scope=spfile sid='*';

System altered.

Both parameters cannot take effect online. We should restart the database.

SQL> shutdown immediate; SQL> startup;

Now we can open the 5th db links.

For more about database links, I have talked about it at: DB Link, How and Why.

Leave a Reply

Your email address will not be published. Required fields are marked *