如何高效从dba_db_links获取其db_name及ip地址用于梳理清晰的数据库迁移数据

测试结论
1,鉴于近期对某客户的数据库进行数据库迁移工作,涉及要梳理大量的db link,为了提升工作效率,特整理如何脚本
2,提取db link之host的数据库名称定义,采用方法为
  instr函数获取service_name的首字符所处位置

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

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

3,说白了就是通过substr和instr组合获取service_name=值的字符串

4,最终的SQL语句为
set linesize 300
col username 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;

OWNER                          DB_LINK                        SERVICE_NAME
—————————— —————————— ——————————————————————————–
SYS                            TARGET_USER_DIRECTSAA2         SERVICE_NAME = esbdbslslslsalla
SYS                            TARGET_USER_DIRECT             SERVICE_NAME = esbdb

5,上述的DB LINK定义语句为
SQL> create database link target_user_directsaa2 connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION = 
  3         (ADDRESS_LIST = 
  4         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521)) 
  5         ) 
  6         (CONNECT_DATA = 
  7          (SERVICE_NAME = esbdbslslslsalla) 
  8         )';

Database link created.

6,获取 SERVICE_NAME与IP地址相关的字符串之SQL

set linesize 300                                             
col username for a40                                         
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;

OWNER                          DB_LINK                        SERVICE_NAME                                       FIRST_IP                       SECOND_IP
—————————— —————————— ————————————————– —————————— ——————————
SYS                            TARGET_USER_DIRECTSAA2         SERVICE_NAME = esbdbslslslsalla                    HOST = 10.0.0.39
SYS                            TARGET_USER_DIRECT             SERVICE_NAME = esbdb                               HOST = 10.0.0.39
SYS                            TARGET_USER_2ADDR              SERVICE_NAME = esbdbslslslsalla                    HOST = 10.0.0.39               HOST = 120.23.30.127

测试明细
1,数据库版本
SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

2,操作系统版本
SQL> host more /etc/*release
::::::::::::::
/etc/lsb-release
::::::::::::::
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
::::::::::::::
/etc/redhat-release
::::::::::::::
Red Hat Enterprise Linux Server release 6.5 (Santiago)
::::::::::::::
/etc/system-release
::::::::::::::
Red Hat Enterprise Linux Server release 6.5 (Santiago)

3,获取db link信息
SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
SQL> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
SYSTEM                         TARGET_USER_ZXY                USER_ZXY                                 tns_esbdb                                          12-JUN-17

SQL> create database link target_user_direct connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION = 
  3         (ADDRESS_LIST = 
  4         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521)) 
  5         ) 
  6         (CONNECT_DATA = 
  7          (SERVICE_NAME = esbdb) 
  8         )';

Database link created.

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

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
SYSTEM                         TARGET_USER_ZXY                USER_ZXY                                 tns_esbdb                                          12-JUN-17
SYS                            TARGET_USER_DIRECT             USER_ZXY                                 (DESCRIPTION =                                     14-JUN-17
                                                                                                              (ADDRESS_LIST =
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
                                                                                                       9)(PORT = 1521))
                                                                                                              )
                                                                                                              (CONNECT_DATA =
                                                                                                               (SERVICE_NAME = esbdb)
                                                                                                              )

4,获取service_name字符串
SQL> create database link target_user_directsaa2 connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION = 
  3         (ADDRESS_LIST = 
  4         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521)) 
  5         ) 
  6         (CONNECT_DATA = 
  7          (SERVICE_NAME = esbdbslslslsalla) 
  8         )';

Database link created.

set linesize 300
col username 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;

OWNER                          DB_LINK                        SERVICE_NAME
—————————— —————————— ——————————————————————————–
SYS                            TARGET_USER_DIRECTSAA2         SERVICE_NAME = esbdbslslslsalla
SYS                            TARGET_USER_DIRECT             SERVICE_NAME = esbdb

5,获取host相关ip地址字符串

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
from 
(
select owner,
       db_link,
       host,
       instr(host,’SERVICE_NAME’) as sern_first_pos,
       substr(host,
           instr(host,’HOST’),
           30) as first_ip_addr
from dba_db_links
where  instr(host,’SERVICE_NAME’)>0
)  first_level;

OWNER                          DB_LINK                        SERVICE_NAME                                                                     FIRST_IP
—————————— —————————— ——————————————————————————– ————————————————————
SYS                            TARGET_USER_DIRECTSAA2         SERVICE_NAME = esbdbslslslsalla                                                  HOST = 10.0.0.39
SYS                            TARGET_USER_DIRECT             SERVICE_NAME = esbdb                                                             HOST = 10.0.0.39

select 
       substr(host,
           instr(host,’HOST’,1),
           30) as first_ip_addr, 
       substr(host,
           instr(host,’HOST’,2),
           30) as second_ip_addr
from dba_db_links
where  instr(host,’SERVICE_NAME’)>0

SQL> create database link target_user_2addr connect to user_zxy identified by system 
  2  using ‘(DESCRIPTION = 
  3         (ADDRESS_LIST = 
  4         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521)) 
  5         ) 
  6         (CONNECT_DATA = 
  7          (SERVICE_NAME = esbdbslslslsalla) 
  8         )
  9         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)
 10         (PORT = 1521)
 11         ‘;

Database link created.

SQL> set linesize 300                                             
SQL> col username for a40                                         
SQL> col db_link for a30                                          
SQL> col host for a50                                             
SQL> select owner,db_link,username,host,created from dba_db_links where instr(host,’SERVICE_NAME’)>0;

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
SYS                            TARGET_USER_DIRECTSAA2         USER_ZXY                                 (DESCRIPTION =                                     14-JUN-17
                                                                                                              (ADDRESS_LIST =
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
                                                                                                       9)(PORT = 1521))
                                                                                                              )
                                                                                                              (CONNECT_DATA =
                                                                                                               (SERVICE_NAME = esbdbslslslsalla)
                                                                                                              )

SYS                            TARGET_USER_DIRECT             USER_ZXY                                 (DESCRIPTION =                                     14-JUN-17
                                                                                                              (ADDRESS_LIST =

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
                                                                                                       9)(PORT = 1521))
                                                                                                              )
                                                                                                              (CONNECT_DATA =
                                                                                                               (SERVICE_NAME = esbdb)
                                                                                                              )

SYS                            TARGET_USER_2ADDR              USER_ZXY                                 (DESCRIPTION =                                     15-JUN-17
                                                                                                              (ADDRESS_LIST =
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
                                                                                                       9)(PORT = 1521))

OWNER                          DB_LINK                        USERNAME                                 HOST                                               CREATED
—————————— —————————— —————————————- ————————————————– ———
                                                                                                              )
                                                                                                              (CONNECT_DATA =
                                                                                                               (SERVICE_NAME = esbdbslslslsalla)
                                                                                                              )
                                                                                                              (ADDRESS = (PROTOCOL = TCP)(HOST = 120.23.3
                                                                                                       0.127)
                                                                                                              (PORT = 1521)

SQL> 

SQL> select instr(host,’HOST’,1,1),
  2         instr(host,’HOST’,1,2)
  3  from dba_db_links where instr(host,’SERVICE_NAME’)>0;

INSTR(HOST,’HOST’,1,1) INSTR(HOST,’HOST’,1,2)
———————- ———————-
                    74                      0
                    74                      0
                    74                    224

set linesize 300                                             
col username for a40                                         
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;

OWNER                          DB_LINK                        SERVICE_NAME                                       FIRST_IP                       SECOND_IP
—————————— —————————— ————————————————– —————————— ——————————
SYS                            TARGET_USER_DIRECTSAA2         SERVICE_NAME = esbdbslslslsalla                    HOST = 10.0.0.39
SYS                            TARGET_USER_DIRECT             SERVICE_NAME = esbdb                               HOST = 10.0.0.39
SYS                            TARGET_USER_2ADDR              SERVICE_NAME = esbdbslslslsalla                    HOST = 10.0.0.39               HOST = 120.23.30.127

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » 如何高效从dba_db_links获取其db_name及ip地址用于梳理清晰的数据库迁移数据

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址