最近生产数据库要修改生产用户的密码，由于用户密码修改，各个库之间创建的dblink就失效了，需要重建。上生产一看dblink还真不少，而且谁建的都有，改起来比较费劲。于是想到可以使用expdp的方式导出所有的dblink，即可获取dblink的创建语句，然后修改原密码再在库上创建就可以了。
生产环境中的库有两个版本10.2.0.4和11.2.0.3下面分别在10g和11g上做测试
测试10g：
sql>select*fromv$version; BANNER ---------------------------------------------------------------- OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64bi PL/sqlRelease10.2.0.4.0-Production CORE 10.2.0.4.0 Production TNSforLinux:Version10.2.0.4.0-Production NLSRTLVersion10.2.0.4.0-Production
创建dblink到11g数据库
sql>createpublicdatabaselinklink_11g connecttosystemidentifiedby"123456" using'(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.2)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=orcl) ) )'; Databaselinkcreated. sql>select*fromdual@link_11g; D - X colownerfora30 colusernamefora30 coldb_linkfora30 colhostfora50 setlinesize200 setpagesize999 selectOWNER,DB_LINK,USERNAME,HOSTfromdba_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~]$expdpsystem/123456directory=dumpdumpfile=dblink.dmpfull=yinclude=db_link Export:Release10.2.0.4.0-64bitProductiononThursday,10November,201615:26:02 Copyright(c)2003,2007,Oracle.Allrightsreserved. Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64bitProduction WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions FLASHBACKautomaticallyenabledtopreservedatabaseintegrity. Starting"SYSTEM"."SYS_EXPORT_FULL_01":system/********directory=dumpdumpfile=dblink.dmpfull=yinclude=db_link EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeDATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:0KB ProcessingobjecttypeDATABASE_EXPORT/SCHEMA/DB_LINK Mastertable"SYSTEM"."SYS_EXPORT_FULL_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforSYSTEM.SYS_EXPORT_FULL_01is: /home/oracle/dblink.dmp Job"SYSTEM"."SYS_EXPORT_FULL_01"successfullycompletedat15:26:13
使用impdp查看导出的dblink的创建语句
[oracle@rhel5~]$impdpsystem/123456directory=dumpdumpfile=dblink.dmpsqlfile=dblink.sql Import:Release10.2.0.4.0-64bitProductiononThursday,201615:28:31 Copyright(c)2003,Oracle.Allrightsreserved. Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64bitProduction WiththePartitioning,DataMiningandRealApplicationTestingoptions Mastertable"SYSTEM"."SYS_sql_FILE_FULL_01"successfullyloaded/unloaded Starting"SYSTEM"."SYS_sql_FILE_FULL_01":system/********directory=dumpdumpfile=dblink.dmpsqlfile=dblink.sql ProcessingobjecttypeDATABASE_EXPORT/SCHEMA/DB_LINK Job"SYSTEM"."SYS_sql_FILE_FULL_01"successfullycompletedat15:28:33 [oracle@rhel5~]$cat/home/oracle/dblink.sql --CONNECTSYSTEM --newobjecttypepathis:DATABASE_EXPORT/SCHEMA/DB_LINK CREATEPUBLICDATABASELINK"LINK_11G" CONNECTTO"SYSTEM"IDENTIFIEDBYVALUES'0588B5151FD4089DC7B87F64727E740D26' USING'(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.2)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=orcl) ) )';
测试11g
sys@ORCL>select*fromv$version; BANNER -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction PL/sqlRelease11.2.0.4.0-Production CORE 11.2.0.4.0 Production TNSforLinux:Version11.2.0.4.0-Production NLSRTLVersion11.2.0.4.0-Production
创建dblink
createpublicdatabaselinklink_10g connecttosystemidentifiedby"123456" using'(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=mydb) ) )'; Databaselinkcreated. sys@ORCL>select*fromdual@link_10g; DUM --- X sys@ORCL>selectOWNER,HOSTfromdba_db_links; OWNER DB_LINK USERNAME HOST -------------------------------------------------------------------------------------------------------------------------------------------- PUBLIC LINK_A SCOTT orcl PUBLIC LINK_10G SYSTEM (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56. 11)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=mydb) ) )
expdp导出dblink
[oracle@rhel6~]$expdpsystem/123456directory=dumpdumpfile=dblink.dmpfull=yinclude=db_link Export:Release11.2.0.4.0-ProductiononThuNov1015:34:242016 Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved. Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction WiththePartitioning,DataMiningandRealApplicationTestingoptions FLASHBACKautomaticallyenabledtopreservedatabaseintegrity. Starting"SYSTEM"."SYS_EXPORT_FULL_01":system/********directory=dumpdumpfile=dblink.dmpfull=yinclude=db_link EstimateinprogressusingBLOCKSmethod... TotalestimationusingBLOCKSmethod:0KB ProcessingobjecttypeDATABASE_EXPORT/SCHEMA/DB_LINK Mastertable"SYSTEM"."SYS_EXPORT_FULL_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforSYSTEM.SYS_EXPORT_FULL_01is: /home/oracle/dblink.dmp Job"SYSTEM"."SYS_EXPORT_FULL_01"successfullycompletedatThuNov1015:34:422016elapsed000:00:15
使用impdp查看导出的dblink的创建语句
[oracle@rhel6~]$impdpsystem/123456directory=dumpdumpfile=dblink.dmpsqlfile=dblink.sql Import:Release11.2.0.4.0-ProductiononThuNov1015:35:382016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions Mastertable"SYSTEM"."SYS_sql_FILE_FULL_01"successfullyloaded/unloaded Starting"SYSTEM"."SYS_sql_FILE_FULL_01":system/********directory=dumpdumpfile=dblink.dmpsqlfile=dblink.sql ProcessingobjecttypeDATABASE_EXPORT/SCHEMA/DB_LINK Job"SYSTEM"."SYS_sql_FILE_FULL_01"successfullycompletedatThuNov1015:35:402016elapsed000:00:01 [oracle@rhel6~]$cat/home/oracle/dblink.sql --CONNECTSYSTEM ALTERSESSIONSETEVENTS'10150TRACENAMECONTEXTFOREVER,LEVEL1'; ALTERSESSIONSETEVENTS'10904TRACENAMECONTEXTFOREVER,LEVEL1'; ALTERSESSIONSETEVENTS'25475TRACENAMECONTEXTFOREVER,LEVEL1'; ALTERSESSIONSETEVENTS'10407TRACENAMECONTEXTFOREVER,LEVEL1'; ALTERSESSIONSETEVENTS'10851TRACENAMECONTEXTFOREVER,LEVEL1'; ALTERSESSIONSETEVENTS'22830TRACENAMECONTEXTFOREVER,LEVEL192'; --newobjecttypepath:DATABASE_EXPORT/SCHEMA/DB_LINK CREATEPUBLICDATABASELINK"LINK_10G" CONNECTTO"SYSTEM"IDENTIFIEDBYVALUES':1' USING'(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=mydb) ) )'; CREATEPUBLICDATABASELINK"LINK_A" CONNECTTO"SCOTT"IDENTIFIEDBYVALUES':1' USING'orcl';
使用上面的方式可以查看dblink的创建语句。
从上面dblink创建语句的输出结果也可以看出10g导出的dblink创建语句可以看到密码的密文，而11g里就看不到了。
官方文档：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505
