I hit this ORA-02020 error for the first time today and it helped me to learn the truth behind the DB_LINK (I have to confess that I did not read before ). The truth is,” if you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link.”
My case was;
I created a cursor and run a statement which uses all the DB_LINKS on the server. The number of db_links I used was more than 30. The code was getting the ORA-02020:too many database links in use error.
The definition and the solution of the error was simple
|Cause:||The current session has exceeded the INIT.ORA open_links maximum.|
|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.|
I checked the open_links parameter (I was unaware that this parameter was existed) and saw that parameter had the default value which was 4 and cannot be modified online. I took the advised action and added commit clause before the cursor gets the new value for the db_link. This solution worked very well.
By the way Document says that, I can close a database link session with “alter session close database link XXXX” clause but it did not worked as I expected. You still need to do commit or rollback before closing a db_link session to do that. Also after commit, the session stays on the remote machine but the sessions are put on list to be closed when a new db_link opened. (list works last in first out (before commit) fashion as can be seen from demonstration ).
The only benefit of alter session close database link is closing a link with knowledge otherwise LIFO will work after commit.