查看dblink_详解生产环境如何简单有效梳理数据库dblink连接

概述

最近接触两个新的数据库，因存在dblink之间连接，所以简单梳理一下dblink，比较适合多个dblink连接情况。

---

思路

提取dblink之host的数据库名称定义，采用方法为

1)instr函数获取service_name的首字符所处位置

2)通过substr基于上述 所处位置获取service_name截至碰到第一个 ) 符号的 字符串

(注：上述字符串宽度约定不超过100，因为可能service_name=值可能大于8)

总之就是通过substr和instr组合获取service_name=值的字符串

---

--查看相关dblink:

set linesize 1000col owner for a40col db_link for a30col service_name for a80select first_level.owner, first_level.db_link, substr(substr(first_level.host, first_level.sern_first_pos, 100), 1, instr(substr(first_level.host, first_level.sern_first_pos, 100), ')') - 1) as service_name from (select owner, db_link, host, instr(host, 'SERVICE_NAME') as sern_first_pos from dba_db_links where instr(host, 'SERVICE_NAME') > 0) first_level;

---

--获取 SERVICE_NAME与IP地址相关的字符串

set linesize 1000 col owner for a20 col db_link for a30 col host for a50col first_ip for a30col second_ip for a30col service_name for a50select first_level.owner, first_level.db_link, substr(substr(first_level.host, first_level.sern_first_pos, 100), 1, instr(substr(first_level.host, first_level.sern_first_pos, 100), ')') - 1) as service_name, substr(first_level.first_ip_addr, 1, instr(first_level.first_ip_addr, ')') - 1) as first_ip, substr(first_level.second_ip_addr, 1, instr(first_level.second_ip_addr, ')') - 1) as second_ip from (select owner, db_link, host, instr(host, 'SERVICE_NAME') as sern_first_pos, substr(host, instr(host, 'HOST'), 30) as first_ip_addr, substr(host, instr(host, 'HOST', 1, 2), 30) as second_ip_addr from dba_db_links where instr(host, 'SERVICE_NAME') > 0) first_level;

---

--获取dblink定义

 select owner,db_link,username,host,created from dba_db_links;

---

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~