- 08 Nov, 2021
- 0 Comments
- 3 Mins Read
Troubleshoot : ORA-02020: too many database links in use
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> 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> 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> select * from dba_db_links; OWNER DB_LINK HOST CREATED ———- ———- ———- ———- ANKUSH REMOTEDB remotedb 12-Oct-2021 18:10:01