Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 21, 2001 - 7:42 pm UTC

Last updated: February 17, 2007 - 10:53 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

I have five questions regarding the open_links parameter.

1) What resources (eg. memory, processes, etc) does each open_link use/require ? For instance, what resources are used when we have open_links = 100 ?

2) How can we monitor the number of active open_links (v$view?) ?

3) Can we manually close an active open_link ?

4) What is the lru algorithm for open_links ?

5) Are there any references that go into details about open_links and how they work ?

Thanks,

Jim

and Tom said...

1) setting open_links to 100 won't have much of an effect on either the PGA or SGA size. It simply sets an upper bound on the number of open connections that will be maintained.

It appears that each dblink takes about 500k of PGA memory after opening.

2) select * from v$dblink
will show you what dblinks are open in the current session.


3) alter session close database link <linkname>

will close them.

4) it just finds the first NON-used link and closes it. If you have open_links set to 1 for example and attempt:

tkyte@TKYTE816> select * from dual@l1;

D
-
X

tkyte@TKYTE816> select * from dual@l5;
select * from dual@l5
*
ERROR at line 1:
ORA-02020: too many database links in use


That fails until you commit (freeing it up)

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> select * from dual@l5;

D
-
X


It is not an LRU, its first I find that is not being used right now in the session gets closed.

5) this chapter in the distributed systems guide covers this (and answers most of the above as well)

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76960/ds_admin.htm#22888 <code>

....

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

close dblink in PL/SQL

June, January 13, 2003 - 11:16 am UTC

Tom,

thanks for all useful information on database links. However I couldn't close dblink in PL/SQL code after I explicitly commit and 'execute immediate alter session close dblink linkname' which raised error of ORA-02020: too many database links in use. I plan to check data in one procedure across 5-7 database links, and I know I could change open_links parameter or break one procedure to two. well, before I bounce production database or schedule two daily jobs in production, I would like to know if it is possible to close dblink in PL/SQL. any suggestion is appreciated.

my piece of code is as following for checking one source system and there will be 5-7 same blocks to check against different source system:

....
sql_stmnt varchar2(50):='ALTER SESSION CLOSE DATABASE LINK';
-- check for MSA source systems
BEGIN

SELECT COUNT(*) INTO V_MSA_CNT
FROM msa.dstrt@msa_link
;

IF V_MSA_CNT IS NULL OR V_MSA_CNT=0 THEN
v_error_value_tx := 'ERROR-NO DATA FOR DSTRT on SOURCE SYSTEM MSA';
pop_batch_error_log(v_objct_nm, NULL, v_error_value_tx, FALSE) ;
v_ABEND_FL:='Y';
END IF;

EXCEPTION

WHEN OTHERS THEN

v_error_code := SQLCODE;
v_error_value_tx := 'ERROR-for MSA on '||V_PROCESS_DT;
pop_batch_error_log(v_objct_nm, NULL, v_error_value_tx, FALSE);
v_ABEND_FL:='Y';

END;

commit;

sql_stmnt1 := sql_stmnt||' msa_link';
EXECUTE IMMEDIATE sql_stmnt1;
dbms_output.put_line(sql_stmnt1);
....
up to check 4 source system, I could get output on screen like
...
ALTER SESSION CLOSE DATABASE LINK msa_link

however, once exceed 4, ORA-02020 raises.



Tom Kyte
January 13, 2003 - 1:00 pm UTC

The problem is that when you statically reference them -- the database is trying to make sure they are all ok and valid before it runs the code.

Use dynamic references and it won't (cannot) try to validate them. The error is not when the code executes but immediately PRIOR.

scott@ORA815> create or replace procedure p
2 as
3 l_cnt number;
4 begin
5 execute immediate 'select count(*) from dual@d1' into l_cnt;
6 commit;
7 execute immediate 'alter session close database link d1';
8
9 execute immediate 'select count(*) from dual@d2' into l_cnt;
10 commit;
11 execute immediate 'alter session close database link d2';
12
13 execute immediate 'select count(*) from dual@d3' into l_cnt;
14 commit;
15 execute immediate 'alter session close database link d3';
16
17 execute immediate 'select count(*) from dual@d4' into l_cnt;
18 commit;
19 execute immediate 'alter session close database link d4';
20
21 execute immediate 'select count(*) from dual@d5' into l_cnt;
22 commit;
23 execute immediate 'alter session close database link d5';
24 end;
25 /

Procedure created.

scott@ORA815>
scott@ORA815> exec p

PL/SQL procedure successfully completed.


That runs -- but if you use static sql for the select's -- it fails.

It works

J, January 14, 2003 - 1:33 pm UTC

Thanks for quick reply. It works!

closing links after select

Scott Wesley, June 10, 2003 - 11:44 pm UTC

G'day

Why do we need to issue a commit, even when we have only *selected* data over the link, before we issue a close.

I created a simple test case, and it always raises
ORA-02080: database link is in use
when I did not have a commit/rollback.

select x into y from z@remote;
exec immediate 'alter session close database link remote';
*
error
ORA-02080: database link is in use

This also happens when I declare an explicit cursor, and close the cursor.

The documentation doesn't seem to cover this issue greatly...

Your assistance is always appreciated.

Thanks.

Tom Kyte
June 11, 2003 - 6:56 am UTC

touching a database link implicitly begins a transaction - you can see that in v$transaction:

ops$tkyte@ORA817DEV> select a.addr , b.sid
  2    from v$transaction a, v$session b
  3   where a.addr = b.taddr
  4     and b.sid = (select sid from v$mystat where rownum = 1)
  5  /

no rows selected

ops$tkyte@ORA817DEV> select * from dual@ora920.us.oracle.com;

D
-
X

ops$tkyte@ORA817DEV> select a.addr , b.sid
  2    from v$transaction a, v$session b
  3   where a.addr = b.taddr
  4     and b.sid = (select sid from v$mystat where rownum = 1)
  5  /

ADDR            SID
-------- ----------
803732B8          8

ops$tkyte@ORA817DEV> alter session close database link ora920.us.oracle.com;
ERROR:
ORA-02080: database link is in use


ops$tkyte@ORA817DEV> commit;

Commit complete.

ops$tkyte@ORA817DEV> alter session close database link ora920.us.oracle.com;

Session altered.

ops$tkyte@ORA817DEV>

"why" -- it just does, always has.  you've done something "complex" -- opened a remote session, that begins a transaction. 

Internal Behaviour

vaibhav, December 17, 2003 - 2:43 am UTC

I would like to know why does oracle needs to start a transaction when it fetches records from the other database.

A handshake mechanism is useful when PL/SQL is at play, but SQL statements like "SELECT" starting a transaction is hard to digest.

Is there a separate engine for processing SQL and PL/SQL? two extra statements (rollback and alter session close dblink) have to be fired, which i suppose are an overhead.

Generally, oracle has a very wonderful and simple way of doing things.Could you please describe as to why oracle behaves in such a manner when it comes to database links ?

Tom Kyte
December 17, 2003 - 6:55 am UTC

as i said once above

...
"why" -- it just does, always has. you've done something "complex" -- opened a
remote session, that begins a transaction.
......

select's can start transactions in a single instance of Oracle -- is there an issue you are having with the fact that the transaction is "started"? If so, we can work on solving that issue (since changing the way it works is not really an option)

Still struggling with this issue

Craig, May 24, 2004 - 10:27 am UTC

Ok, so I've got a package with 15 procedures, each of which has an insert query, where I select across a db link (via synonym) and insert into a local table. Every night when this package is called, the first procedure fails with an ora-2020 too many database links in use. However, every other procedure in that package runs without issue, and if I try again to execute the procedure that failed, it runs with no issue.

Can you help me understand this?



Tom Kyte
May 24, 2004 - 11:04 am UTC

is this package the FIRST thing run in that session?

or, have you run some other stuff, and this other stuff used dblinks as well, and this other stuff had not yet committed or rolled back -- and you are committing/rolling back (clearing out the way for more dblinks to be opened)



Just when I thought I had seen it all....

Craig, May 24, 2004 - 4:51 pm UTC

Well, Mr. Kyte, despite the fact that I thought you were sending me on a wild goose chase, I decided to look at the package that is executed before my "trouble" package gets called. I knew it used db links; shame on me for not mentioning it in my previous post. But I was also *certain* that each procedure committed properly. To my amazement, the last 4 procedures (and 6 in total) did not commit. (I'll deal with that developer later GRRRR!!) I've since added the commits in the proper places. We'll see at 2:15 am CDT whether or not that does the trick. I'll follow up then.

Thanks again so much!!

Tom Kyte
May 24, 2004 - 6:58 pm UTC

let us know how it goes for sure, thats the only thing I could think of -- and I didn't see anything in the problem database about it..

Bad news on the doorstep...

Craig, May 25, 2004 - 9:13 am UTC

Well, that didn't solve my problems, and now I have a new problem. My wife is sick of the phone ringing at 2:15am. Frankly, so am I. So let's see if I can tell you anything else that might help. When the session in question begins, it executes probably 50 procedures from 6 different packages. These procedures use db links, materialized views, mv's over db links...whatever else you can think of. All procedures have commits, and all these procedures execute serially.

Then comes my problem procedure. When it is called, there is only one other user in the db, but he is not using any db links to do his thing.

So what am I overlooking?

Tom Kyte
May 25, 2004 - 11:11 am UTC

it is the cumulative dblinks that are out there. they are still "referenced" for some reason.

open transactions that need to be committed.
open cursors that still reference the dblink.
(the other users don't matter, open_links is a 'session thing')

couple of ideas for you

o on the far end of the scale, simply have your script that calls you disconnect/connect again, that will close them all.

o set open_links higher, allow for more concurrently open links

o issue alter session close database link in the procedures that use them, after a commit, to totally close them.

o write a generic routine that loops over all database links you can "see" in the data dictionary and close them. Call this routine prior to yours being invoked (the very act of calling your routine will touch those dblinks potentially)

Followup on db_links

SJ, February 16, 2007 - 9:31 am UTC

Tom,

Issuing commit works perfectly fine in sqlplus ..once commit is issued, the db links usage counter gets reset fine..however, when I do the same in pl/sql ( procedure ) commit seems not be having any impact ..here is an example..Might be I am doing something wrong.
CREATE OR REPLACE PROCEDURE conf_dblink_dbt2 AS
m_out4 NUMBER;
m_out5 NUMBER;
BEGIN
begin
commit;
select 1 INTO m_out4 from evid_asr where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt2..evidasr ..'||sqlerrm);
end;
begin
commit;
select 1 INTO m_out5 from pmt_info where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt2 ..pmtinfo'||sqlerrm);
END;
end;


CREATE OR REPLACE PROCEDURE conf_dblink_dbt1 AS
m_out1 NUMBER;
m_out2 NUMBER;
m_out3 NUMBER;
begin
BEGIN
select 1 INTO m_out1 from cabs_ban where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt1 ..cabsban'||sqlerrm);
end;
begin
select 1 INTO m_out2 from cta_asr@cafe2_e2e.world where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt1 ..asre2e'||sqlerrm);
end;
begin
select 1 INTO m_out3 from cta_asr@cafe2ste.world where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt1 ..asrste'||sqlerrm);
end;
COMMIT;
begin
conf_dblink_dbt2;
commit;
exception
when others then
dbms_output.put_line('first ..'||sqlerrm);
end;
end;

All the tables used here are over a dblink.

When I exit and connect back and run the above procedure..I get the following:

SQL> set serverout on size 100000
SQL> exec conf_dblink_dbt1;
conf_dblink_dbt2 ..pmtinfoORA-02020: too many database links in use

PL/SQL procedure successfully completed.

Any help on how I can reset the db links counter usage inside PL/SQL? I appreciate your help.
Tom Kyte
February 17, 2007 - 10:53 am UTC

alter session can be used


please - for the love of whatever - do not ever every do this:

exception
when others then
dbms_output.put_line('first ..'||sqlerrm);
end; 


just delete that code.....

update

SJ, February 16, 2007 - 9:48 am UTC

Tom,

My open_link is set to 5 and I am using oracle 9204. In the db links I mentioned one db link is using Oracle Transparent Gateway accessing DB2 .

Code is
CREATE OR REPLACE PROCEDURE conf_dblink_dbt2 AS
    m_out4 NUMBER;
   m_out5 NUMBER;
  BEGIN
begin
commit;
  select 1 INTO m_out4 from evid_asr where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt2..evidasr ..'||sqlerrm);
end;
begin
commit;
  select 1 INTO m_out5 from pmt_info where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt2 ..pmtinfo'||sqlerrm);
  END;
end;


CREATE OR REPLACE PROCEDURE conf_dblink_dbt1 AS
    m_out1 NUMBER;
    m_out2 NUMBER;
    m_out3 NUMBER;
begin
  BEGIN
  select 1 INTO m_out1 from cabs_ban where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt1 ..cabsban'||sqlerrm);
end;
begin
  select 1 INTO m_out2 from cta_asr@cafe2_e2e.world where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt1 ..asre2e'||sqlerrm);
end;
begin
  select 1 INTO m_out3 from cta_asr@cafe2ste.world where rownum=1;
exception
when others then
dbms_output.put_line('conf_dblink_dbt1 ..asrste'||sqlerrm);
end;
  COMMIT;
begin
 conf_dblink_dbt2;
commit;
exception
when others then
dbms_output.put_line('first ..'||sqlerrm);
end;
end;
 /