5

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Khaled
  • 841
  • 3
  • 13
  • 22

6 Answers6

8

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);
Pawel Solarski
  • 1,028
  • 8
  • 7
2

I'm not sure you can create a query to check live db links. One thing you could do is create a table updated by a background process with the list of db links and for each of them a 'last time seen alive' timestamp

vc 74
  • 37,131
  • 7
  • 73
  • 89
2

Any link could have a problem due to different categories of issues:

  • invalid link definition: wrong username, password (if used), service name

  • remote account locked

  • remote db configuration (e.g. sessions per user exceeded)

  • remote db or host unavailability

  • network connectivity

Given the changing nature of these failure modes there can't be a dictionary view (for example) that describes the state of the link. An asynchronous process that checks in the background will also stand a chance of being out-of-date. Probably the lightest-weight test you can do is issue a "select sysdate from dual@remote_db" before you need to use the link in your code

dpbradley
  • 11,645
  • 31
  • 34
  • 1
    Thanks for ur answer,but the problem i face with this approach is waiting till i get the timeout exception.so if any of the database links is not available for any reason,i will have to wait till the server stops trying to connect ! – Khaled Oct 03 '10 at 14:17
1

You could write an OS level script that performs a tnsping, since db links usually depend on the tnsnames.ora anyway.

Stellios
  • 742
  • 3
  • 7
  • For Windows... tnsping ... check the %errorlevel% value immediately after the tnsping. If it is 0 == OK, 1 == ERROR. For UNIX return code is $? (errorlevel equivalent). – Stellios Oct 04 '10 at 23:53
  • The dblink could have a bad password and tnsping wouldn't know. – Tulains Córdova Jul 14 '15 at 20:28
1

You could use WITH FUNCTION and do 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 result you will get OK or error message.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

I don't know if you managed to get this done, but I wanted to do something like this and check which database links are active. I found this on another forum 

Select * from v$dblink

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

yhw42
  • 3,334
  • 2
  • 27
  • 24
Sushant Nayak
  • 1