No question at this time
DBA Top 10
1 M. Cadot 20800
2 A. Kavsek 14900
3 M. Hidayathullah ... 10800
4 B. Vroman 7900
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1600
10 A. Hudspith 1500
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 users48052
Total active users1733
Act. users last 24h10
Act. users last hour1
Registered user hits last week395
Registered user hits last month1204
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, 75 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, 75 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, 74 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, 59 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, 59 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, 57 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, 57 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, 57 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, 57 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, 57 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, 57 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