Geek Answers Handbook

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

I have a main database with only configuration data at headquarters and several databases in different branches. I created a database link for each branch server.

In some case, I would like to request all valid links (since some links may be invalid due to connection problems or something else), so my question is: check if the database link really has a connection timeout problem Is there an SQL statement to allow the oracle server to perform this check and return only valid database references?

+5
source share
6 answers

, live db. , , , , db " , "

+2

db, :

select * from dual@my_db_link;

, db:

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;

, my_db_links (id, name, status (0,1)) :

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

- :

  • :     , ( ),    

  • db (, )

, (), . , , . , , , - " sysdate from dual @remote_db",

+2

script, tnsping, db tnsnames.ora .

+1

, , - , .

Select * from v$dblink

which shows only active dblinks. Again, this will only work if you have permission to access v$dblink.

0
source

You can use WITH FUNCTIONand perform a simple check:

WITH FUNCTION check_dblink(p_dblink IN VARCHAR2) RETURN VARCHAR2 IS
   r INT;
BEGIN
    EXECUTE IMMEDIATE 'SELECT 1 FROM dual@"' || p_dblink || '"' INTO r;
    RETURN 'OK';

    EXCEPTION
       WITH OTHERS THEN
          RETURN SQLERRM;
END;
SELECT check_dblink(db_link), udl.*
FROM user_db_links udl;

As a result, you will receive a message OKor error message.

0
source

More articles:


All Articles