expdp 只导出dblink

最近生产数据库要修改生产用户的密码，由于用户密码修改，各个库之间创建的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

expdp 只导出dblink

猜你在找的Oracle相关文章