May 12, 2014

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
SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK (dblink_name);

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
----------           ----------         ----------   ----------
GOURANG      REMOTEDB    remotedb   12-May-2014 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.

Thanks
Please feel free to post comments...


No comments:

Post a Comment

Translate >>