最近生产数据库要修改生产用户的密码,由于用户密码修改,各个库之间创建的dblink就失效了,需要重建。上生产一看dblink还真不少,而且谁建的都有,改起来比较费劲。于是想到可以使用expdp的方式导出所有的dblink,即可获取dblink的创建语句,然后修改原密码再在库上创建就可以了。
生产环境中的库有两个版本10.2.0.4和11.2.0.3下面分别在10g和11g上做测试
测试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
CORE10.2.0.4.0Production
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=db