-- 测试连通性函数
CREATE OR REPLACE FUNCTION P_DbLinkIsOk(DbLinkName VARCHAR2) RETURN BOOLEAN IS
BEGIN
  EXECUTE IMMEDIATE 'select 1 from dual@' || DbLinkName;
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;

-- 创建DBLINK连接用于测试
create database link dblink1 connect to loge identified by 123456 using 
'  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )'

-- 测试-正常
BEGIN
  IF P_DbLinkIsOk('dblink1') THEN
    dbms_output.put_line('dblink连接正常');
  ELSE
    dbms_output.put_line('dblink连接异常');
  END IF;
END;

-- 测试-异常
BEGIN
  IF P_DbLinkIsOk('dblink2') THEN
    dbms_output.put_line('dblink连接正常');
  ELSE
    dbms_output.put_line('dblink连接异常');
  END IF;
END;