如何在 Oracle 中检查数据库链接是否有效？

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

提问by Khaled

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?

在某些情况下，我想查询所有有效链接（因为某些链接可能由于连接问题或其他任何原因而无效），所以我的问题是如何检查数据库链接是否有效而不会出现连接超时问题。是否有 SQL 语句让 oracle 主服务器执行该检查并仅返回有效的数据库链接？

采纳答案by vc 74

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

我不确定您是否可以创建查询来检查实时数据库链接。您可以做的一件事是创建一个由后台进程更新的表，其中包含数据库链接列表，并为每个链接创建一个“最后一次看到活着”的时间戳

回答by Pawel Solarski

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:

最后，您可以创建表 my_db_links(id, name, status(0,1)) 并更新它：

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

回答by dpbradley

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

鉴于这些故障模式的不断变化的性质，不可能有描述链接状态的字典视图（例如）。在后台检查的异步进程也有可能过时。在您需要使用代码中的链接之前，您可以执行的最轻量级测试可能是发出“select sysdate from dual@remote_db”

回答by Stellios

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

您可以编写一个执行 tnsping 的操作系统级脚本，因为无论如何 db 链接通常都依赖于 tnsnames.ora。

回答by Lukasz Szozda

You could use WITH FUNCTIONand do simple check:

您可以使用WITH FUNCTION并进行简单检查：

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 OKor error message.

结果，您将收到OK或错误消息。

回答by Sushant Nayak

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.

它只显示活动的 dblink。同样，这仅在您有权访问v$dblink.