On Oracle DB Links Connections

Something I learned today: Running queries that use multiple DB links keep those connections open until you terminate the session or those references.

In my case I was running multiple SELECT statements:

SELECT * FROM table@dbLink_1...
SELECT * FROM table@dbLink_2...
SELECT * FROM table@dbLink_3...
...
SELECT * FROM table@dbLink_n...

At one point I got the “ORA-02020: too many database links in use” error. Turns out this happens if the current session has exceeded the INIT.ORA open_links defined maximum connections. It also turns out that the solution is something simple:

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.

Be Sociable, Share!

•