No question at this time
DBA Top 10
1 M. Cadot 16900
2 A. Kavsek 15000
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4700
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48095
Total active users1706
Act. users last 24h8
Act. users last hour0
Registered user hits last week389
Registered user hits last month1490
Go up

Sql script to find the count of dblink which is not accessible
Next thread: GI Ihstallation for standalone database
Prev thread: end of file communication error

Message Score Author Date
Dear friends,Like we have dblink d1, d2, Oracle s...... Prasathi Ji Aug 03, 2017, 09:25
As a starter: <pre>create or replace function c...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Michel Cadot Aug 03, 2017, 12:29
Dear Michael, Thanks for the prompt response an...... Prasathi Ji Aug 03, 2017, 15:10
Hello Michael, How can we get DB Links names wh...... Taoqir Hassan Aug 18, 2017, 15:04
Just slightly modify the procedure I posted: <p...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Michel Cadot Aug 18, 2017, 15:44
No rows selected, while I have six dblinks which a...... Taoqir Hassan Aug 21, 2017, 08:10
Create and execute the following procedure: <pr...... Michel Cadot Aug 21, 2017, 09:23
Hello, Here is the output. <pre> --Function cr...... Taoqir Hassan Aug 21, 2017, 09:31
1/ I asked the result of the last function I pos...... Michel Cadot Aug 21, 2017, 09:35
Hi Taokir, Please change data dictionary name ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Prasathi Ji Aug 21, 2017, 09:41
Hello, Now it works for me, I have changed user_d...... Taoqir Hassan Aug 21, 2017, 10:09

Follow up by mail Click here


Subject: Sql script to find the count of dblink which is not accessible
Author: Prasathi Ji, India
Date: Aug 03, 2017, 09:25, 133 days ago
Os info: Rhel 6
Oracle info: Oracle 11g
Message: Dear friends,Like we have dblink d1, d2, Oracle scripts vd3, d4. If d4 dblink is down and user is not able to connect using d4 dblink then it will show the count 1.
This will show the count of dblink if any dblink connectivity is fail.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Michel Cadot, France
Date: Aug 03, 2017, 12:29, 133 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message:
As a starter:
create or replace function check_bad_links return integer as

cnt pls_integer := 0;
dum varchar2(1);
begin
for dblink in (select db_link from user_db_links) loop
begin
execute immediate 'select dummy from dual@'||dblink.db_link into dum;
exception when others then cnt := cnt+1;
end;
end loop;
return cnt;
end;
/

SQL> select check_bad_links from dual;
CHECK_BAD_LINKS
---------------
2

1 row selected.


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Prasathi Ji, India
Date: Aug 03, 2017, 15:10, 133 days ago
Message: Dear Michael,

Thanks for the prompt response and wonderful solution.

Best Regards
Prasathi
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Taoqir Hassan, Pakistan
Date: Aug 18, 2017, 15:04, 118 days ago
Message: Hello Michael,

How can we get DB Links names which are not working? Thanks,


Regards,
Taoqir
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Michel Cadot, France
Date: Aug 18, 2017, 15:44, 118 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message:
Just slightly modify the procedure I posted:
create or replace function check_bad_links return sys.odcivarchar2list as

res sys.odcivarchar2list := sys.odcivarchar2list();
dum varchar2(1);
begin
for dblink in (select db_link from user_db_links) loop
begin
execute immediate 'select dummy from dual@'||dblink.db_link into dum;
exception when others then
res.extend;
res(res.count) := dblink.db_link || ': ' || sqlerrm;
end;
end loop;
return res;
end;
/

SQL> select * from table(check_bad_links);
COLUMN_VALUE
-------------------------------------------------------------------------------
MIK8.MYSERVER: ORA-12518: TNS:listener could not hand off client connection
MIK9.MYSERVER: ORA-12518: TNS:listener could not hand off client connection

2 rows selected.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Taoqir Hassan, Pakistan
Date: Aug 21, 2017, 08:10, 115 days ago
Message: No rows selected, while I have six dblinks which are not accessible.

Regards,
Taoqir
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Michel Cadot, France
Date: Aug 21, 2017, 09:23, 115 days ago
Message:
Create and execute the following procedure:
create or replace function list_count_links return integer as

cnt pls_integer := 0;
dum varchar2(1);
begin
for dblink in (select db_link from user_db_links) loop
cnt := cnt+1;
dbms_output.put_line(dblink.db_link);
begin
execute immediate 'select dummy from dual@'||dblink.db_link into dum;
exception when others then null;
end;
end loop;
return cnt;
end;
/


Then copy and paste the result here.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Taoqir Hassan, Pakistan
Date: Aug 21, 2017, 09:31, 115 days ago
Message: Hello, Here is the output.


--Function created
CHECK_BAD_LINKS
(, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , )


--note Query result taken from Toad.

Regards,
Taoqir
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Michel Cadot, France
Date: Aug 21, 2017, 09:35, 115 days ago
Message:
1/ I asked the result of the last function I posted not the previous one.
2/ Forget TOAD, it is b..., use SQL*Plus.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Prasathi Ji, India
Date: Aug 21, 2017, 09:41, 115 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Taokir,

Please change data dictionary name
user_db_links to all_db_links.
It will work.

It is awesome function developed by Michael.You will not find in any blog or website.

Regards
Prasathi

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Sql script to find the count of dblink which is not accessible
Author: Taoqir Hassan, Pakistan
Date: Aug 21, 2017, 10:09, 115 days ago
Message: Hello, Now it works for me, I have changed user_db_links to all_db_links , and run it on SQL PLus.

Thanks Michel for this wonderful function.

Regards,
Taoqir
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here