Home » SQL & PL/SQL » SQL & PL/SQL » using more than four db links in a procedure (Oracle 10G)
using more than four db links in a procedure [message #590355] Wed, 17 July 2013 08:50 Go to next message
athar.fitfd@hotmail.com
Messages: 188
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 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
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.

Re: using more than four db links in a procedure [message #590359 is a reply to message #590355] Wed, 17 July 2013 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: using more than four db links in a procedure [message #590360 is a reply to message #590355] Wed, 17 July 2013 08:55 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 188
Registered: October 2007
Location: pakistan
Senior Member
thanks for the quick reply.
where to write roll back or commit.
Re: using more than four db links in a procedure [message #590362 is a reply to message #590359] Wed, 17 July 2013 08:57 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 188
Registered: October 2007
Location: pakistan
Senior Member
i have the second option. how to close db links in a procedure body.
Re: using more than four db links in a procedure [message #590365 is a reply to message #590362] Wed, 17 July 2013 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
Re: using more than four db links in a procedure [message #590366 is a reply to message #590365] Wed, 17 July 2013 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
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
Re: using more than four db links in a procedure [message #590367 is a reply to message #590362] Wed, 17 July 2013 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: using more than four db links in a procedure [message #590370 is a reply to message #590366] Wed, 17 July 2013 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
Re: using more than four db links in a procedure [message #590389 is a reply to message #590370] Wed, 17 July 2013 10:09 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 188
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
Re: using more than four db links in a procedure [message #590390 is a reply to message #590389] Wed, 17 July 2013 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
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
Re: using more than four db links in a procedure [message #590394 is a reply to message #590389] Wed, 17 July 2013 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: using more than four db links in a procedure [message #590399 is a reply to message #590394] Wed, 17 July 2013 12:51 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 188
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
Re: using more than four db links in a procedure [message #590402 is a reply to message #590399] Wed, 17 July 2013 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: using more than four db links in a procedure [message #590417 is a reply to message #590402] Thu, 18 July 2013 00:52 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 188
Registered: October 2007
Location: pakistan
Senior Member
exactly i made error somewhere so i am here.
Re: using more than four db links in a procedure [message #590418 is a reply to message #590417] Thu, 18 July 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So fix your error.

Regards
Michel
Re: using more than four db links in a procedure [message #590931 is a reply to message #590418] Wed, 24 July 2013 04:55 Go to previous message
athar.fitfd@hotmail.com
Messages: 188
Registered: October 2007
Location: pakistan
Senior Member
i increased the open dblinks parameter . and its done. thanks everybody for helping
Previous Topic: Function to split a string
Next Topic: Date Values difference
Goto Forum:
  


Current Time: Sat Dec 20 18:36:34 CST 2014

Total time taken to generate the page: 0.10148 seconds