測試結論
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

如何高效從dba_db_links獲取其db_name及ip地址用於梳理清晰的資料庫遷移資料

相關文章