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.