厦门特产,牟星,香蕉君-萝卜新闻-国际创业新动向-足球产业最全内容

频道:最近大事件 日期: 浏览:272

概述

最近触摸两个新的数据库,因存在dblink之间衔接,所以简略整理一下dblink,比较合适多个dblink衔接状况。


思路

提取dblink之host的数据库称号界说,选用办法为

1)instr函数获取service_name的首字符所在方位

2)经过substr根据上述 所在方位获取service_name到碰到第一个 ) 符号的 字符串

(注:上述字符串宽度约好不超越100,由于或许service_name=值或许大于8)

总归便是经过substr和instr组合获取service_name=值的字符串


--检查相关dblink:

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;


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

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;


--获取dblink界说

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


后边会共享更多devops和DBA方面的内容,感兴趣的朋友能够重视一下~