Blog

Learnomate Technologies > Blog > Oracle DBA > Troubleshoot : ORA-02020: too many database links in use

Troubleshoot : ORA-02020: too many database links in use

  • November 8, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

TROUBLESHOOT : ORA-02020: TOO MANY DATABASE LINKS IN USE

Resolve “ORA-02020: too many database links in use” error

One of my database is used as remote database for local / distributed databases. For one requirement we have created ‘n’ number of db links. So many queries fired to use the dblink and at that time we found below error:

“ORA-02020: too many database links in use “

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

To find:

SQL> show parameter open_links;

NAME TYPE VALUE
———————————— ———– ——————————
open_links integer 4
open_links_per_instance integer 4

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.

To Change: 

SQL> alter system set open_links_per_instance=10 scope=spfile;
SQL> alter system set open_links=10 scope=spfile;

and then, bounce the database.

Note:

> The default value is set to 4.

> If it is set to 0, distributed transactions are not allowed.

> If you are expecting your transactions to have a maximum of 3 database links open concurrently, set this parameter to 3 or higher.

> Do not set it too high, it is better for the application to close database links when no longer in use than to change the parameter to a high number.

If you are not sure how many database links are opened up concurrently by your session’s database application, you can query v$dblink.

SQL> select in_transaction, count(*) from v$dblink
group by in_transaction;

IN_ COUNT(*)
— ———-
YES 1

Extra Coverage: :

Close a db link To explicitly close the database link , use the command below:

SQL> alter session close database link remotedb; — remotedb –> a dblink name
Session altered.

OR

<br />SQL&gt; exec DBMS_SESSION.CLOSE_DATABASE_LINK (dblink_name);<br />

Know your open links

Once you have created and made use of a database link in Oracle, you might want to keep an eye on the number of concurrent open database links in your database so you can tune the open_links initialization parameter.

Read more here on limiting concurrent open links

You will need to query v$dblink to see how many links are open in your session:

SQL&gt; select in_transaction, count(*) from v$dblink
group by in_transaction;

IN_ COUNT(*)
— ———-
YES 1

Here are some interesting columns and descriptions of v$dblink:

db_link Db link name owner_id Owner name logged_on Is the database link currently logged on? protocol Dblink’s communications protocol open_cursors Are there any cursors open for the db link ? in_transaction Is the db link part of a transaction which has not been commited or rolled back yet ? update_sent Was there an update on the db link ?

dba_db_links

To gather information on all database links in your database, query dba_db_links. You will need dba privileges to see this view, the alternatives are user_db_links and all_db_links.

A user who does not have dba privileges can query all_db_links to see which db links are available to him.

SQL&gt; select * from dba_db_links;

OWNER DB_LINK HOST CREATED
———- ———- ———- ———-
ANKUSH REMOTEDB remotedb 12-Oct-2021 18:10:01

Some interesting columns:

owner User who owns the db link, will state ‘PUBLIC’ if it is a public database link. db_link Db link name. username Username that was specified if it was hardcoded during the create statement, null if not specified during the create statement. host The tnsnames alias specified during the create statement. created Date and time of link creation.

ORA-02020