Limit Oracle Database Links
before implementing them on a production server.
per session in your database
Each session in your database has a limited number of database links that can be opened at the same time.
This number is determined by a database-wide initialization parameter: OPEN_LINKS.
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.
(Read up here for the syntax of the close database link command. )
OPEN_LINKS cannot be modified in real-time, so you will have to change
it in the spfile or pfile and bounce the database.
SQL> alter system set open_links=10 scope=both;
alter system set open_links=0 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter
cannot be modified
SQL> alter system set open_links=10 scope=spfile;
System altered.
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
Next: How to manage database link usage in your database
Click here to read about troubleshooting database link errors.
