Skip to content

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

  • by

ORA-02020

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;

SYSDATE
-------------------
2017-10-10 19:58:16

SQL> select sysdate from dual@dblink02;

SYSDATE
-------------------
2017-10-10 19:58:22

SQL> select sysdate from dual@dblink03;

SYSDATE
-------------------
2017-10-10 19:58:33

SQL> select sysdate from dual@dblink04;

SYSDATE
-------------------
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

Rationale

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.

Solutions

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.

Or by package DBMS_SESSION.CLOSE_DATABASE_LINK.

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.

2. Raise OPEN_LINKS

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.