However, when connected as one user, you can validate the database links for that user and any public links. Validating the database links for other users doesn't seem to be possible from that single connection. You can't prefix the database link name with the schema, as database links themselves can contain dots. Thus, the schema prefix will be treated as part of the link name, causing the statement to fail (because the link cannot be found under that name).
Then I figured that using a stored procedure created in the owner schema could validate the database link in that schema, when created with definer's rights (the default). And because you can use dynamic SQL ("execute immediate" in this case), you can create the procedure in the other schema, invoke it to test the database link in that same schema, and drop it afterwards.
-- Execute as SYS user (because of ORA-01031)
CREATE OR REPLACE PROCEDURE test_any_db_link ( p_owner IN varchar2
, p_link IN varchar2
)
AS
l_owner varchar2(100);
BEGIN
IF p_owner = 'PUBLIC' THEN
l_owner := 'SYSTEM';
ELSE
l_owner := p_owner;
END IF;
--
BEGIN
EXECUTE IMMEDIATE 'create procedure '||l_owner||'.test_any_db_link_temp '||
'as '||
' l_dummy varchar2(1); '||
'begin '||
' select * into l_dummy from dual@'||p_link||'; '||
' dbms_output.put_line(''OK : '||p_owner||'.'||
p_link||'''); '||
'exception '||
' when others then '||
' dbms_output.put_line(''NOK: '||p_owner||'.'||
p_link||'''||'' ''||
sqlcode); '||
'end;';
EXECUTE IMMEDIATE 'begin '||l_owner||'.test_any_db_link_temp; end;';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERR: '||p_owner||'.'||p_link||' '||sqlcode);
END;
EXECUTE IMMEDIATE 'drop procedure '||l_owner||'.test_any_db_link_temp';
END;
/
set serveroutput on size 1000000;
spool test_db_links
BEGIN
FOR r_stmt IN (select 'begin test_any_db_link('''||owner||''','''||
db_link||'''); end;' stmt
from dba_db_links
order by owner, db_link
)
LOOP
execute immediate r_stmt.stmt;
END LOOP;
END;
/
spool off
drop procedure test_any_db_link;
-- done
Main points about this code:
- You execute it as SYS, because of an ORA-01031 (insufficient privileges) otherwise
- The script creates a procedure for every test, and drops it afterwards
- The status of a link is either OK (everything checks out), or ERR (the procedure could not be created, because the link is not valid). In theory, the status could be NOK, when the link is valid, but something else prevents the execution of "select * from dual" from succeeding. So far, I never saw an NOK code
- This script was used on database versions ranging from 9.2 to 12.1
- The error output is very short, you could enhance this with dbms_utility.format_error_stack or something similar
- When a database link is marked as "ERR", investigate further. I have some "ExtProc" call type database links, and those were marked as "ERR", because there is no dual at the other end
- For PUBLIC links, I translated the user for the procedure to SYSTEM, as you cannot create a procedure under the "schema PUBLIC"
