远程oracle导出全库,ORACLE expdp导出远程库指定用户数据到本地数据库

众所周知，ORACLE导出远程库时，我们可以使用exp，但是exp的效率不高，相比之下expdp可以开并行，速度更可控，下面就说下使用dblink结合expdp导出远程库指定用户到本地数据库的方法。首先，在本地库创建发起expdp及dblink的用户，并赋予合适的权限[oracle@linux ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0

weixin_39995774

2925人浏览 · 2021-04-02 21:34:30

weixin_39995774 · 2021-04-02 21:34:30 发布

众所周知，ORACLE导出远程库时，我们可以使用exp，但是exp的效率不高，相比之下expdp可以开并行，速度更可控，

下面就说下使用dblink结合expdp导出远程库指定用户到本地数据库的方法。

首先，在本地库创建发起expdp及dblink的用户，并赋予合适的权限

[oracle@linux ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 09:29:38 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user zhul identified by zhul;

User created.

SQL> grant dba to zhul;

Grant succeeded.

其次，本地创建dblink

SQL> create public database link orcl10g31

connect to zhul identified by zhul

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.31)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl10g)

)

) ';

Database link created.

再次，本地创建expdp客户端使用的directory路径

SQL> create directory zhul as '/home/oracle/';

Directory created.

测试dblink对远程库的访问可用性

SQL> select count(*) from scott.emp@orcl10g31;

COUNT(*)

----------

SQL>

使用expdp及创建好的dblink导出远程库的指定用户

[oracle@linux ~]$ expdp zhul/zhul directory=zhuldumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_exp.log schemas=shijiazhuang network_link=orcl10g31

Export: Release 11.2.0.4.0 - Production on Tue Jul 19 09:35:24 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ZHUL"."SYS_EXPORT_SCHEMA_01": zhul/******** directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_exp.log schemas=shijiazhuang network_link=orcl10g31

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SHIJIAZHUANG"."MR_RESULT" 162.1 KB 1132 rows

. . exported "SHIJIAZHUANG"."MR_POINTS_DEDUCTION" 30.12 KB 349 rows

. . exported "SHIJIAZHUANG"."MR_CONFIG" 9.445 KB 5 rows

. . exported "SHIJIAZHUANG"."MR_CONFIG_LOG" 6.117 KB 5 rows

. . exported "SHIJIAZHUANG"."MR_REGION" 5.468 KB 13 rows

Master table "ZHUL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ZHUL.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/shijiazhuang20160819.dmp

Job "ZHUL"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 19 09:35:55 2016 elapsed 0 00:00:30

在本地创建好接收远程库指定用户数据的本地用户，然后使用impdp导入远程库指定用户的数据到本地指定用户下

SQL> create user shijiazhuang identified by shijiazhuang default tablespace shijiazhuang;

User created.

SQL> grant resource to shijiazhuang;

Grant succeeded.

SQL> grant create session to shijiazhuang;

Grant succeeded.

[oracle@linux ~]$ impdp zhul/zhul directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_imp.log schemas=shijiazhuang

Import: Release 11.2.0.4.0 - Production on Tue Jul 19 09:36:50 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ZHUL"."SYS_IMPORT_SCHEMA_01": zhul/******** directory=zhul dumpfile=shijiazhuang20160819.dmp logfile=shijiazhuang20160719_imp.log schemas=shijiazhuang

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SHIJIAZHUANG" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SHIJIAZHUANG"."MR_RESULT" 162.1 KB 1132 rows

. . imported "SHIJIAZHUANG"."MR_POINTS_DEDUCTION" 30.12 KB 349 rows

. . imported "SHIJIAZHUANG"."MR_CONFIG" 9.445 KB 5 rows

. . imported "SHIJIAZHUANG"."MR_CONFIG_LOG" 6.117 KB 5 rows

. . imported "SHIJIAZHUANG"."MR_REGION" 5.468 KB 13 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "ZHUL"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue Jul 19 09:37:06 2016 elapsed 0 00:00:15

使用过程中可能遇到的问题：

创建dblink时尽量不要使用tnsname解析，要使用tnsname里的服务全称，否则导出时会遇到如下报错：

C:\Users\localadmin>expdp zhul/zhul directory=myexpdp dumpfile=shijiazhuang20160

819.dmp logfile=shijiazhuang20160719.log schemas=shijiazhuang network_link=orcl1

0g31

Export: Release 11.2.0.1.0 - Production on 星期二 7月 19 09:15:04 2016

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc

tion

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39001: 参数值无效

ORA-39200: 链接名称 "orcl10g31" 无效。

ORA-02019: 未找到远程数据库的连接说明

不要在异构平台下使用expdp，例如：windows导出远程linux/unix下oracle的用户数据，然后通过ftp上传到linux/unix，再使用impdp导入，否则本地导入会遇到如下报错而失败：

[oracle@linux ~]$ impdp shijiazhuang/shijiazhuang DIRECTORY=zhul dumpfile=shijiazhuang20160719.dmp logfile=shijiazhuang20160719_imp.log

Import: Release 11.2.0.4.0 - Production on Tue Jul 19 09:28:52 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39143: dump file "/home/oracle/shijiazhuang20160719.dmp" may be an original export dump file

[oracle@linux ~]$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29357786/viewspace-2122216/，如需转载，请注明出处，否则将追究法律责任。

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Springboot+MySQL 实现从数据库获取数据展示到前端

1.运行效果效果图如下：2.创建数据库表数据库名为"springboot",数据库表名为"user"，数据库表里面放了5条数据，字段分别为：“id”，“name”，“age”，“email”。3.新建SpringBoot项目1、选择Spring Initial...

腾讯云开发者社区

数据库实验二数据库的简单查询和连接查询实验

– l）用Transact-SQL语句表示下列操作．在学生选课库中实现其数据查询操作．– ①求数学系学生的学号和姓名。select sno,sname from student where sdept='MA';– ②求选修了课程的学生学号。select distinct sno from sc;– ③求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。s

腾讯云开发者社区

Spring Boot Mybatis数据库datasource多数据源配置

application.yaml文件配置spring:datasource:db1:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.0.1:3306/ellassay_dev?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBe