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.
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.
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 ?
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?
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!!
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?
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.
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;
/