How to check if a database link is valid in Oracle?

Question

I have a main database with only setup data at the headquarter and several databases at different branches.I created a database link for each branch server.

In some case I would like to query all the valid links (as some links could be invalid due to connection problems or anything else),so my question is How to check if the database link is valid without getting in Connection timeout problems. Is there a SQL statement to let the oracle main server do that check and return only the valid database links?

Answer 1

You can verify db link by executing:

select * from dual@my_db_link;

To can create function that verifies db link:

function is_link_active(
  p_link_name varchar2
) return number is
  v_query_link varchar2(100) := 'select count(*) alive from dual@'||p_link_name;
  type db_link_cur is REF CURSOR;
  cur db_link_cur;
  v_status number;
begin
  open cur FOR v_query_link; 
  loop
    fetch cur INTO v_status; 
    exit when cur%notfound;
    dbms_output.put_line('v_status='||v_status);
    return v_status;
  end loop;
  close cur;
exception when others then
  close cur;
  return 0; 
end is_link_active;

Lastly, you can create table my_db_links(id, name, status(0,1)) and update it:

update 
  my_db_links mdl
set
  mdl.status = is_link_active(mdl.name);