ORA-02020: too many database links in use

Mehmet Salih Deveci

5 years ago

Hi,

You can get “ORA-02020: too many database links in use ” error during the drop any user.

Details of error are as follows.

ORA-02020: too many database links in use

Cause: The current session has exceeded the INIT.ORA open_links maximum.

Action: 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.

select * from dual@DBL_MSD
*
ERROR at line 1:
ORA-02020: too many database links in use

Check the existing Database links as follows.

SQL>  select db_link,logged_on,open_cursors from v$dblink;

To solve this error, You should increase the open_links parameter as follows.

alter system set open_links=20 scope=spfile sid='*';
alter system set open_links_per_instance=20 scope=spfile sid='*';


Then restart database to activate this parameter as follows.
 
shutdown immediate;
startup

If you don’t want to increase open_links because of restart database, then you can close the existing database links as follows.

SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK (Database_Link_Name);

SQL> alter session close database link Database_Link_Name;

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/