获取oracle数据库的ip地址,如何高效从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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-2141173/，如需转载，请注明出处，否则将追究法律责任。