expdp 只导出dblink

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了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

原文链接：https://www.f2er.com/oracle/211862.html

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问