January 16, 2008

If you access a database link in a session, then the link remains open until you close the session (ORA-02020)

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 ).

Coskan, there is another thing to consider regarding db links. Even a select from a db link starts a distributed transaction and gets an undo block. If the user or the application doing the select does not commit for hours that undo block remains active and cannot be overwritten. This prevents that undo segment from wrapping and may cause it to extend. If there are lots of sessions doing this your undo usage may go up.

Digging through the ODA Guide link above, I find that one of the ‘rules’ of pl/sql is that is can’t run system commands like alter session. I’m not sure where that leaves me; my link/session is definitely still open after the procedure runs.

However, since my stored procedure is being executed via an oracle job, I just added the ALTER statement to the job script itself. That seems to work so far.

I have a problem where one of my packages goes over a db link to dump data. When the db link is down the package gets stuck. Is there a way to test db link so that if it is not up I get an error message?

Hi , i am writing a trigger on db1 which uses a cursor refering to table T1 of db2 using db link1. now in this cursor
again i called a procedure (of db2) which in turn inserts a row into table T2 of db3 using db link 2 . when i exec this
procedure i am getting “ORA-01775: looping chain of synonyms “. can’t we use the db link in this way. pl help

How can this error be reproduced when only a single DBLINK is used several times in a stored procedure?

I have this error in a procedure that only uses 2 DBLINK calls (both of them are selects).
The procedure is used by a web application so there may be more users running it at the same time.
How can I reproduce this?

open_links=4
Yes, this error only appears from time to time. The site is in production for more than a year now and this error has only recently appeared. The code related to DBLINK access hasn’t changed significantly in the last year. So I am assuming that the increased number of concurrent users accessing the site might cause this error. Let’s assume that there are 3 users connected at the site at the same time. They are all using the same DB user to connect to the DB. If all of them are calling the procedure with 2 DBLINKs calls at the same time, will 02020 error be generated?

I am no longer positive the place you are getting your info,
but good topic. I needs to spend some time studying more
or working out more. Thank you for magnificent info I
used to be looking for this information for my mission.