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

Question

Hi All,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?

Thanks in advance

Khaled

Answer 1

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

Answer 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

Answer 3

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