OraFAQ Forum: SQL & PL/SQL » using more than four db links in a procedure

Home » SQL & PL/SQL » SQL & PL/SQL » using more than four db links in a procedure (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using more than four db links in a procedure [message #590355]

Wed, 17 July 2013 08:50

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

hi all,

i want to use more than four db links in my stored procedure to retrieve data from different databases.
but it says ORA-02020: too many database links are opened.
i also wrote execute immediate to close some links but its not working.

please help.

Regards
Athar

[Updated on: Wed, 17 July 2013 08:53]

Report message to a moderator

Re: using more than four db links in a procedure [message #590357 is a reply to message #590355]

Wed, 17 July 2013 08:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

02020, 00000, "too many database links in use"
// *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.

my car is not working
tell me how to make my car go.

Report message to a moderator

Re: using more than four db links in a procedure [message #590359 is a reply to message #590355]

Wed, 17 July 2013 08:55

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ask you DBA to increase open_links parameter or close your database links when you no more use them.

Regards
Michel

Report message to a moderator

Re: using more than four db links in a procedure [message #590360 is a reply to message #590355]

Wed, 17 July 2013 08:55

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

thanks for the quick reply.
where to write roll back or commit.

Report message to a moderator

Re: using more than four db links in a procedure [message #590362 is a reply to message #590359]

Wed, 17 July 2013 08:57

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

i have the second option. how to close db links in a procedure body.

Report message to a moderator

Re: using more than four db links in a procedure [message #590365 is a reply to message #590362]

Wed, 17 July 2013 08:58

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

you don't close DB links. You finish transactions that use those links by calling commit or rollback.

Report message to a moderator

Re: using more than four db links in a procedure [message #590366 is a reply to message #590365]

Wed, 17 July 2013 09:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

cookiemonster wrote on Wed, 17 July 2013 06:58

you don't close DB links. You finish transactions that use those links by calling commit or rollback.

consider what is DOCUMENTED below

http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin004.htm

Report message to a moderator

Re: using more than four db links in a procedure [message #590367 is a reply to message #590362]

Wed, 17 July 2013 09:04

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

execute immediate 'alter session close database ...';

But you cannot close a db link before the end of the transaction unless you made no modification through this db link.

Regards
Michel

Report message to a moderator

Re: using more than four db links in a procedure [message #590370 is a reply to message #590366]

Wed, 17 July 2013 09:07

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

[quote title=BlackSwan wrote on Wed, 17 July 2013 15:00]cookiemonster wrote on Wed, 17 July 2013 06:58

consider what is DOCUMENTED below

Serves me right for relying on the action against the error message.

Report message to a moderator

Re: using more than four db links in a procedure [message #590389 is a reply to message #590370]

Wed, 17 July 2013 10:09

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

my scenario is.

begin
select c1 into v_c1  from t1@db1;
select c1 into v_c1  from t1@db2;
select c1 into v_c1  from t1@db3;
select c1 into v_c1  from t1@db4;
select c1 into v_c1  from t1@db5;
end;

it works fine for four statements. when add the fifth one. my procedure compiles with 1 error mentioned above.
even i used commit and close database link using execute immediate. but no luck

Report message to a moderator

Re: using more than four db links in a procedure [message #590390 is a reply to message #590389]

Wed, 17 July 2013 10:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> show parameter open

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
open_links                           integer     4
open_links_per_instance              integer     4
read_only_open_delayed               boolean     FALSE
session_max_open_files               integer     10

need to increase the values of OPEN_LINK & OPEN_LINKS_PER_INSTANCE parameters

Report message to a moderator

Re: using more than four db links in a procedure [message #590394 is a reply to message #590389]

Wed, 17 July 2013 11:19

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

athar.fitfd@hotmail.com wrote on Wed, 17 July 2013 17:09

my scenario is.
begin
select c1 into v_c1  from t1@db1;
select c1 into v_c1  from t1@db2;
select c1 into v_c1  from t1@db3;
select c1 into v_c1  from t1@db4;
select c1 into v_c1  from t1@db5;
end;
it works fine for four statements. when add the fifth one. my procedure compiles with 1 error mentioned above.
even i used commit and close database link using execute immediate. but no luck

Are you unable to read?

Michel Cadot wrote on Wed, 17 July 2013 15:55

Ask you DBA to increase open_links parameter or close your database links when you no more use them.

Regards
Michel

Michel Cadot wrote on Wed, 17 July 2013 16:04

execute immediate 'alter session close database ...';
But you cannot close a db link before the end of the transaction unless you made no modification through this db link.

Regards
Michel

Report message to a moderator

Re: using more than four db links in a procedure [message #590399 is a reply to message #590394]

Wed, 17 July 2013 12:51

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

Mr. micheal

as i said earlier that i did try to close the db link by using execute immediate but my procedure still not compiles.

Regards
Athar

Report message to a moderator

Re: using more than four db links in a procedure [message #590402 is a reply to message #590399]

Wed, 17 July 2013 13:23

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So you made error but we can't say which one as you don't show us.
Anyway, what I said works... if you do it properly.

Regards
Michel

Report message to a moderator

Re: using more than four db links in a procedure [message #590417 is a reply to message #590402]

Thu, 18 July 2013 00:52

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

exactly i made error somewhere so i am here.

Report message to a moderator

Re: using more than four db links in a procedure [message #590418 is a reply to message #590417]

Thu, 18 July 2013 01:09

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So fix your error.

Regards
Michel

Report message to a moderator

Re: using more than four db links in a procedure [message #590931 is a reply to message #590418]

Wed, 24 July 2013 04:55

athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan

Senior Member

i increased the open dblinks parameter . and its done. thanks everybody for helping

Report message to a moderator

Previous Topic:	Function to split a string
Next Topic:	Date Values difference

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 23 09:59:35 CDT 2025