ORA-02020: too many database links in use || ORA-02080: database link is in use

Problem
I was trying to test database links in a database, but ran into following error:
ORA-02020: too many database links in use

Cause
"The current session has exceeded the INIT.ORA open_links maximum."
The default "open_links" system parameter value is set to 4.

SQL> show parameters open_links;
NAME                                           TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                                     integer          4
open_links_per_instance                integer          4


Solution
"Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases."
1 Using following command to check db links status:
select db_link, logged_on from v$dblink;
The above command will show database links created. V$DBLINK  will lists all open/active database links in your session


2 For an indication on how long the dblink has been open, run:
            select t.addr, s.sid, s.username, s.machine, s.status,
            (sysdate - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 as hours_active
            from v$transaction t, v$session s
            where t.addr = s.taddr;


Note: How to know if a transaction is local or distributed?
            Check v$global_transaction

3 Run following commands to dynamically generate a file to check db links status:

SQL>spo dblinks.lst
SQL>select db_link from dba_db_links;
SQL>spo off;
$for i in `cat dblinks.lst`; do echo "select count(*) from user_tables@$i;" >> check_dblinks.sql; echo "rollback;" >>check_dblinks.sql; done

Related Links

1. If you access a database link in a session, then the link remains open until you close the session (ORA-02020)
2. Troubleshoot oracle database link errors
3. How to Close and Expire Database Link [ID 473635.1]