open_links parameter - Ask TOM (2022)

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.

open_links parameter - Ask TOM (1)

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.

open_links parameter - Ask TOM (2)

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 selectedops$tkyte@ORA817DEV> select * from dual@ora920.us.oracle.com;D-Xops$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 8ops$tkyte@ORA817DEV> alter session close database link ora920.us.oracle.com;ERROR:ORA-02080: database link is in useops$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 ?

open_links parameter - Ask TOM (3)

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?

open_links parameter - Ask TOM (4)

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

open_links parameter - Ask TOM (5)

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?

open_links parameter - Ask TOM (6)

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.

open_links parameter - Ask TOM (7)

February 17, 2007 - 10:53 am UTC

alter session can be used

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

exceptionwhen others thendbms_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; BEGINbegincommit; select 1 INTO m_out4 from evid_asr where rownum=1;exceptionwhen others thendbms_output.put_line('conf_dblink_dbt2..evidasr ..'||sqlerrm);end;begincommit; select 1 INTO m_out5 from pmt_info where rownum=1;exceptionwhen others thendbms_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;exceptionwhen others thendbms_output.put_line('conf_dblink_dbt1 ..cabsban'||sqlerrm);end;begin select 1 INTO m_out2 from cta_asr@cafe2_e2e.world where rownum=1;exceptionwhen others thendbms_output.put_line('conf_dblink_dbt1 ..asre2e'||sqlerrm);end;begin select 1 INTO m_out3 from cta_asr@cafe2ste.world where rownum=1;exceptionwhen others thendbms_output.put_line('conf_dblink_dbt1 ..asrste'||sqlerrm);end; COMMIT;begin conf_dblink_dbt2;commit;exceptionwhen others thendbms_output.put_line('first ..'||sqlerrm);end;end; /

FAQs

What is the default value for Idle_time in Oracle? ›

So the value for IDLE_TIME is Default.

What is process limit in Oracle? ›

Process Limit Usage (%)

The PROCESSES initialization parameter specifies the maximum number of operating system user processes that can simultaneously connect to a database at the same time. This number also includes background processes utilized by the instance.

How many Oracle processes are running? ›

Oracle has at least 8 processes running which run the db. Like RMON etc. You can ask the DB which queries are running as that just a table query.

How do I get the top 100 records in Oracle SQL Developer? ›

SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause
  1. SQL Server / MS Access Syntax: SELECT TOP number|percent column_name(s) FROM table_name. ...
  2. MySQL Syntax: SELECT column_name(s) FROM table_name. ...
  3. Oracle 12 Syntax: SELECT column_name(s) ...
  4. Older Oracle Syntax: SELECT column_name(s) ...
  5. Older Oracle Syntax (with ORDER BY): SELECT *

What is Sqlnet Inbound_connect_timeout? ›

Answer: The sqlnet. inbound_connect_timeout parameter is used to limit the time, set in seconds, for a client to connect with the database server and provide the required authentication information.

How do I change idle time in Oracle? ›

CONNECT_TIME and IDLE_TIME in Oracle profile
  1. Check the idle time and connect time value for a profile. SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME','CONNECT_TIME');
  2. Alter value of idle time. ...
  3. Alter value of connect time. ...
  4. For using resource limit parameter should be true.
5 Dec 2018

Top Articles

You might also like

Latest Posts

Article information

Author: Reed Wilderman

Last Updated: 09/15/2022

Views: 6059

Rating: 4.1 / 5 (72 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Reed Wilderman

Birthday: 1992-06-14

Address: 998 Estell Village, Lake Oscarberg, SD 48713-6877

Phone: +21813267449721

Job: Technology Engineer

Hobby: Swimming, Do it yourself, Beekeeping, Lapidary, Cosplaying, Hiking, Graffiti

Introduction: My name is Reed Wilderman, I am a faithful, bright, lucky, adventurous, lively, rich, vast person who loves writing and wants to share my knowledge and understanding with you.