最近生产数据库要修改生产用户的密码，由于用户密码修改，各个库之间创建的dblink就失效了，需要重建。上生产一看dblink还真不少，而且谁建的都有，改起来比较费劲。于是想到可以使用expdp的方式导出所有的dblink，即可获取dblink的创建语句，然后修改原密码再在库上创建就可以了。

Oracle 测试10g：

SQL> select * from v$version;
?
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE? ? 10.2.0.4.0? Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

创建dblink到11g数据库

SQL> create public database link link_11g
connect to system identified by "123456"
using '(DESCRIPTION =
? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
? ? (CONNECT_DATA =
? ? ? (SERVER = DEDICATED)
? ? ? (SERVICE_NAME = orcl)
? ? )
? )';?
?
Database link created.
SQL> select * from dual@link_11g;
?
D
-
X
?
col owner for a30
col username for a30
col db_link for a30
col host for a50
set linesize 200
set pagesize 999
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links;
OWNER? ? ? ? ? ? ? ? ? DB_LINK? ? ? ? ? ? ? ? USERNAME? ? ? ? ? ? ? ? HOST
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
PUBLIC? ? ? ? ? ? ? ? LINK_11G? ? ? ? ? ? ? SYSTEM? ? ? ? ? ? ? (DESCRIPTION =
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2)(PORT = 1521))
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (CONNECT_DATA =
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (SERVER = DEDICATED)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (SERVICE_NAME = orcl)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? )

使用expdp导出dblink

[oracle@rhel5 ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp full=y include=db_link
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 November, 2016 15:26:02
Copyright (c) 2003, 2007, Oracle.? All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":? system/******** directory=dump dumpfile=dblink.dmp full=y include=db_link
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
? /home/oracle/dblink.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:26:13

使用impdp查看导出的dblink的创建语句