最近接觸兩個新的數據庫，因存在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方面的内容，感興趣的朋友可以關注一下~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!