概述
最近接触两个新的数据库,因存在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;
![d5825ae1ce09378913b4f57fcf04181c.png](https://i-blog.csdnimg.cn/blog_migrate/528430fbbb2bf0b727917796f2a427fb.jpeg)
--获取 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;
![1d1759ac92a145b7606195fbf2ccb32a.png](https://i-blog.csdnimg.cn/blog_migrate/3495365e5bc00093eb7cf731c5edbf37.jpeg)
--获取dblink定义
select owner,db_link,username,host,created from dba_db_links;
![6ee7ab059c4b3ab377a2ba1d90a14351.png](https://i-blog.csdnimg.cn/blog_migrate/ece0e33eaed1818701fc0304c44c633a.jpeg)
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
![1c294d906a295dbed7575fc11fa8e7f7.gif](https://i-blog.csdnimg.cn/blog_migrate/147eb7bb0d73fc6cf47924c1883a0d9c.gif)