Coskan’s Approach to Oracle

January 16, 2008

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

Filed under: Basics — coskan @ 5:11 pm

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

Cole Listing 9

The only benefit of alter session close database link is closing a link with knowledge otherwise LIFO will work after commit.

 References Used;

 Oracle® Database Administrator’s Guide 10g Release 2 (10.2)

About these ads

30 Comments »

  1. AFAIK there are at least two parameters that have an influence on distributed connections/transactions.

    OPEN_LINKS and DISTRIBUTED_LOCK_TIMEOUT

    Comment by Marco Gralike — January 16, 2008 @ 6:41 pm

  2. Also from within the database a ROLLBACK FORCE sometimes will work

    Comment by Marco Gralike — January 16, 2008 @ 6:48 pm

  3. Thank you for the comments Marco.

    Comment by coskan — January 16, 2008 @ 8:42 pm

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

    YAS@10G>select * from dual@DBLINK1.WORLD;

    D

    X

    YAS@10G>select sid from v$mystat where rownum=1;

    SID
    ———-
    146

    YAS@10G>select t.used_ublk,t.used_urec from v$session s,v$transaction t
    2 where s.taddr=t.addr
    3 and s.sid=146;

    USED_UBLK USED_UREC
    ———- ———-
    1 1

    YAS@10G>commit;

    Commit complete.

    YAS@10G>select t.used_ublk,t.used_urec from v$session s,v$transaction t
    2 where s.taddr=t.addr
    3 and s.sid=146;

    no rows selected

    Comment by Yas — January 18, 2008 @ 7:24 am

  5. Hi,
    I have been working on an Oracle DBA/Sysadmin Dashboard. What do you think are the key ingredients of a DBA dashboard?

    Your opinion is highly regarded
    Regards
    Nilesh
    Dashboards

    Comment by njethwa — February 11, 2008 @ 11:58 pm

  6. I’m accessing the database link through a stored procedure. I can’t figure out how to call ALTER SESSION CLOSE DATABASE LINK XXX, or the pl/SQL equivalent.

    A. Do I need to worry about this, or does the session/link automatically close when the procedure closes? (I’m not sure what exactly is considered a session.)

    B. If I need to close the link, how in a stored procedure?

    thanks for your input ,

    David

    Comment by David — April 11, 2008 @ 10:55 pm

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

    Script Text: (‘prod’ is the DB Link)

    EXECUTE EOD_PROD.ProcessEndofDay(0);
    alter session close database link prod;

    Comment by David — April 12, 2008 @ 12:19 am

  8. David, you need to use the EXECUTE IMMEDIATE instruction from pl/sql code–just enclose your alter command in quotes

    EXECUTE IMMEDIATE ‘ALTER SESSION CLOSE DATABASE LINK PROD';

    you can accomplish a lot of non pl/sql things including DDL this way from code (like truncating tables, etc.)

    Comment by Kevin — June 30, 2008 @ 9:10 pm

  9. Hi all,

    How can I set ACTION of a session created by Oracle when I use a database link.

    Following is an example:

    — In DB1
    SQL>CONN user1/hispassword@db1
    Connected.

    SQL> EXEC SYS.DBMS_APPLICATION_INFO.SET_ACTION(‘TEST ACTION’);

    PL/SQL procedure successfully completed.

    SQL> SELECT ACTION FROM V$SESSION WHERE USERNAME=’USER1′;

    ACTION
    ——————————–
    TEST ACTION

    SQL> SELECT SYSDATE FROM DUAL@DB2;

    SYSDATE
    ———
    23-JAN-09

    — In DB2

    SQL> SELECT ACTION FROM V$SESSION WHERE USERNAME=’USER1′;
    ACTION
    ——————————–

    Comment by Baraka — January 23, 2009 @ 6:03 am

  10. Hi Baraka

    This link gives you the answer by an Oracle ACE Justin Cave

    http://forums.oracle.com/forums/thread.jspa?threadID=850440&tstart=0

    Comment by coskan — January 23, 2009 @ 2:09 pm

  11. 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?

    Thanks,

    Comment by AB — September 16, 2009 @ 7:33 am

  12. Hi AB,

    The only method I know is trying to select over remote db and do error handling in case you get an exception

    Comment by coskan — September 16, 2009 @ 7:43 am

  13. Good and useful information

    Comment by Roopavathy — January 28, 2010 @ 7:24 am

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

    Comment by supriya — August 31, 2010 @ 7:01 am

  15. The SQL documentation on the ALTER SESSION CLOSE DATABASE LINK command at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#i2231814 does say “The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS”.

    In PLSQL, rather than using EXECUTE IMMEDIATE, use DBMS_SESSION.CLOSE_DATABASE_LINK. See http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sessio.htm#i1011038

    Comment by Hemant K Chitale — January 11, 2011 @ 6:38 am

  16. 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?

    Comment by Adrian — February 23, 2011 @ 12:31 pm

    • adrian

      Are you calling your procedure several times and get the error after a certain amount of calling or 1 time and every time you call you hit the problem ?

      Meanwhile what is your open_links parameter value ?

      Comment by coskan — February 23, 2011 @ 12:35 pm

  17. 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?

    Comment by Adrian — February 23, 2011 @ 12:42 pm

    • I think its better you increase the open_links parameter and also change the code to close the links you opened in your procedure

      Comment by coskan — February 23, 2011 @ 12:53 pm

  18. Thanks, I will do that.

    Comment by Adrian — February 23, 2011 @ 1:02 pm

  19. you need geld for a own bussines come on here is it…

    If you access a database link in a session, then the link remains open until you close the session (ORA-02020) « Coskan’s Approach to Oracle…

    Trackback by you need geld for a own bussines come on here is it — March 29, 2012 @ 12:02 pm

  20. Geld need anyone people of this World come here and take it…

    If you access a database link in a session, then the link remains open until you close the session (ORA-02020) « Coskan’s Approach to Oracle…

    Trackback by Geld need anyone people of this World come here and take it — March 29, 2012 @ 12:23 pm

  21. I used to be able to find good information from your content.

    Comment by sami — April 24, 2012 @ 3:46 pm

  22. Nice post and discussion after…
    Just to mention, on RAC there is one another parameter “open_links_per_instance”.
    Rg,
    Damir

    Comment by Damir Vadas — June 27, 2012 @ 5:54 am

  23. Hi! I simply wish to offer you a huge thumbs up for your great information
    you’ve got right here on this post. I will be coming back to your blog for more soon.

    Comment by Bitvisitor — August 10, 2012 @ 1:17 pm

  24. Eu gosto que as informações sobre papo. Obrigado.

    Comment by como arrumar um namorado — July 30, 2013 @ 4:14 am

  25. Hello! Do you know if they make any plugins to safeguard against hackers?
    I’m kinda paranoid about losing everything I’ve worked
    hard on. Any tips?

    Comment by Turismo Dentale Polonia — August 6, 2013 @ 4:58 am

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

    Comment by www — June 22, 2014 @ 7:36 am

  27. fantastic issues altogether, you simply received a brand new reader.
    What might you suggest about your subnit that you simply made some days in the past?
    Anyy positive?

    Comment by www.panoseuraa.fi — August 17, 2014 @ 3:17 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: