This blog is intended for DBA’s who have error while using a database links.Let’s work on a journey to decode, troubleshoot, and over the challenge posed by the ORA-02020 error.
Issue:
Error While Fetching data against a database link, got below error.ORA-02020: Too Many Database Links In Use
Sql>select sysdate from dual@Daatbase_link5;
*
ERROR at line 1:
ORA-02020: too many database links in use
CAUSE & SOLUTION:
n open_links parameter control, the number of database links each session can use without closing it.
n If you access a database link in a session, then the link remains open until you close the session.
Check the below parameter:
SQL> show parameter open_link
NAME TYPE VALUE
———————————— ———– ——————————
open_links integer 4
open_links_per_instance integer 4
Here open_links is set to 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
n Close the open db_link connections
n Increase the open_links parameter (bounce required)
Let’s reproduce this error.
SQL> select sysdate from dual@Database_link1;
SYSDATE
———
30-JUL-17
SQL> select sysdate from dual@Database_link2;
SYSDATE
———
30-JUL-17
SQL> select sysdate from dual@Database_link3;
SYSDATE
———
30-JUL-17
SQL> select sysdate from dual@Database_link4;
SYSDATE
———
30-JUL-17
SQL> select sysdate from dual@Database_link5;
select sysdate from dual@Database_link5
*
ERROR at line 1:
ORA-02020: too many database links in use
n Now we reached maximum open database link connections.
n View the open database link connection[Need to run this from same session ]
n 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
—————- — ————
Database_link1 YES 0
Database_link2 YES 0
Database_link3 YES 0
Database_link4 YES 0
n We can see there are 4 open database link transactions and it is matching the open_links parameter. So quick way to fix is to close these connections.
SQL> alter session close database link Database_link4;
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 or we can use 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;
DB_LINK LOG OPEN_CURSORS
—————- — ————
Database_link1 YES 0
Database_link2 YES 0
Database_link3 YES 0
SQL> select sysdate from dual@Database_link5;
SYSDATE
———
30-JUL-17
Solution:2
n Alternative solution for this issue is given below,Increase the open_links parameter in spfile and restart the database it will works.
SQL> alter system set open_links=8 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
Hope the solution would have helped to overcome the above issues. Happy Debugging!
