如何高效从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