db2 添加数据库连接（详解生产环境如何简单有效梳理数据库dblink连接）

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

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

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

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

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

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

set linesize 1000 col owner for a40 col db_link for a30 col service_name for a80 select 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;

set linesize 1000 col owner for a20 col db_link for a30 col host for a50 col first_ip for a30 col second_ip for a30 col service_name for a50 select 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;

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

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