ORA-02020: too many database links in use

Error :

ORA-02020: too many database links in use


Solution :


Increase the open_links and open_links_instance parameter in the DB . Bounce Database











Or

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')

This is equivalent to

SQL>alter session close database link "link name";

However, it is not always feasible to change existing code. A possible solution might be to create a procedure that will close all open db links and run it after or before queries that uses database links.

CREATE OR REPLACE procedure USERNAME.rollback_and_close_db_links  AS
begin
    rollback;
    for links in (select db_link from gv$dblink) loop
        execute immediate 'alter session close database link '||links.db_link;
    end loop;
end;
/


Execute the Procedure:- 

SQL> execute ROLLBACK_AND_CLOSE_DB_LINKS

PL/SQL procedure successfully completed.

SQL> exec ROLLBACK_AND_CLOSE_DB_LINKS


PL/SQL procedure successfully completed.