Oracle 数据泵（expdp/impdp）导入导出方法教程

Oracle数据泵导入导出是日常工作中常用的基本技术之一，我们使用oracle数据库数据泵导入(impdp)导出(expdp)进行数据库备份，数据库迁移等数据库维护工作。本文主要说明oracle数据库导入导出的命令。

天下英雄出我辈，一入江湖岁月催
我是爱生活的「无间行者」，努力把实践过的解决方案分享给大家
如果这篇文章对你有用，一个赞、一个评论、一个关注，我都很开心，给点鼓励吧，让我知道你在看。

目录

• Oracle 数据泵（expdp/impdp）导入导出方法教程
• • 使用场景：
  • 参与本教程的素材
  • Oracle表空间：
• 数据泵导出（expdp）：
• • 准备工作
  • 导出语法
• 数据泵导入（impdp）：
• • 准备工作
  • 导入语法
• 实战演练
• • 针对数据库用户表的复制(改变表的owner)
  • • 环境准备
    • 导出（expdp）
    • 导入（impdp）1
    • ORA-31684: 对象类型已存在
    • 导入（impdp）2
    • 小结

使用场景：

在工作中，涉及到的Oracle数据库迁移，备份，还原等，可以使用本教程数据泵导入导出来解决。欢迎补充指导。

参与本教程的素材

1. Oracle11g
2. 可视化工具为Oracle SQL Developer
3. windows命令行

Oracle表空间：

Oracle数据库被划分成称作为表空间的逻辑区域——形成Oracle数据库的逻辑结构。
一个Oracle数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。
表空间是Oracle数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。
每个Oracle数据库均有SYSTEM表空间,这是数据库创建时自动创建的。
SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它Oracle软件产品要求的表)。

数据泵导出（expdp）：

准备工作

--准备工作EXPNC_DIR路径创建
--查询数据库路径表
select * from DBA_DIRECTORIES;
--查询数据库用户表
select * from DBA_USERS;
--查询数据库表空间
select * from DBA_TABLESPACES;
--查询数据库数据文件信息表
select * from DBA_DATA_FILES;
123456789

--创建逻辑路径
create directory EXPNC_DIR as 'E:\oracletablespace\expnc';
--命令行执行 expdp操作

cmd->[导出语法]
12345

导出语法

需要在命令行中执行

--1)按用户导
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1
--2)并行进程parallel

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
--3)按表名导

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1
--4)按查询条件导

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'
--5)按表空间导

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example
--6)导整个数据库

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y
1234567891011121314151617

数据泵导入（impdp）：

准备工作

--创建表空间
create tablespace "user_new"
DATAFILE 'd:\oracle_tablespace\user_new'
size 500M AUTOEXTEND on next 100M
maxsize unlimited logging extent
management local segment space management auto;
--是否提前创建用户设置默认表空间

create user users2 IDENTIFIED BY 123 default tablespace "user_new";
grant connect,resource to users2;
--命令行执行 impdp操作

cmd->[导入语法]
1234567891011121314

导入语法

需要在命令行中执行

--1)导到指定用户下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
--2)改变表的owner

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
--3)导入表空间

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
--4)导整个数据库

impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
--5)追加数据

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION
1234567891011121314

实战演练

针对数据库用户表的复制(改变表的owner)

我们一般使用Oracle是以用户对象为单位的数据库表空间。
本次实战内容为

1. 将users用户下所有的表导出成dmp文件
2. 使用此dmp文件将数据恢复到users2用户下。

环境准备

用system管理员登录，创建表空间

C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 5月 6 13:08:55 2019
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
请输入用户名:  system

输入口令:
连接到:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create SMALLFILE tablespace "user"
 DATAFILE 'E:\oracletablespace\user'
 size 500M AUTOEXTEND on next 100M
 maxsize unlimited logging extent
 management local segment space management auto;
表空间已创建。
1234567891011121314151617181920

创建users用户，设置表空间，并授权

SQL> create user users IDENTIFIED BY 123 default tablespace "user";
用户已创建。
SQL> grant connect,resource to users;
授权成功。
1234

退出并切换到users用户

创建student表

SQL> create table student(
  2    id varchar2(32 BYTE),
  3    name varchar2(32 BYTE),
  4    phone varchar2(16 BYTE),
  5    email varchar2(64 BYTE)
  6  );
表已创建。

SQL> insert into student values('1','赵大','123','123@qq.com');

已创建 1 行。

SQL> insert into student values('2','钱二','234','234@qq.com');

已创建 1 行。

SQL> insert into student values('3','李三','345','345@qq.com');

已创建 1 行。
1234567891011121314

导出（expdp）

EXPNC_DIR目录创建

SQL> create directory EXPNC_DIR as 'E:\oracletablespace\expnc';
目录已创建。
--如果是expdp users/123@orcl 这个用户是普通用户，需要读写授权
SQL> grant read,write on directory EXPNC_DIR to users;
授权成功。
12345

执行 数据泵（expdp ）导出

cmd> expdp system/password@orcl schemas=xhs dumpfile=expdp_users.dmp DIRECTORY=EXPNC_DIR
1

EXPNC_DIR目录下文件已产生

此时我们再往users用户的student表中再插入两条数据,以此区别两个用户表中的数据

SQL>insert into student values('4','刘四','444','444@qq.com');
已创建 1 行。
SQL>insert into student values('5','王五','555','555@qq.com');
已创建 1 行。
1234

导入（impdp）1

使用system管理员创建新的用户users2

执行 数据泵（impdp ）导入

cmd>impdp system/password@orcl DIRECTORY=EXPNC_DIR DUMPFILE=expdp_users.dmp REMAP_SCHEMA=users:users2
1

ORA-31684: 对象类型已存在

操作成功完成，但是出现了错误【ORA-31684: 对象类型已存在】这是因为impdp执行的时候会去主动创建一个users2用户，而我们已经提前创建过用户了

我们查看一下导入之后的结果，发现两个用户的student表是不同的

导入（impdp）2

删除复制好的users2,此次导入不提前创建用户来规避错误【ORA-31684: 对象类型已存在】

SQL> drop TABLESPACE "user_new" INCLUDING CONTENTS AND DATAFILES;
表空间已删除
SQL> drop user users2 cascade;
用户已删除。
1234

执行 数据泵（impdp ）导入

cmd>impdp system/password@orcl DIRECTORY=EXPNC_DIR DUMPFILE=expdp_users.dmp REMAP_SCHEMA=users:users2
1

不再提示错误【ORA-31684: 对象类型已存在】

小结

导入1和导入2的区别：

1. 导入1提前创建了目标用户users2并指定了表空间"user_new"，即用户users的表空间是"user"，用户users2的表空间是"user_new"
2. 导入2是由impdp命令默认创建了用户users2，两个用户的表空间都是"user"。

这里要说一下，
一个数据库可以有多个实例，
一个实例可以有多个用户（不同实例下允许相同名字的用户存在），
一个用户只能分配一个表空间（不同用户下允许相同名字的表存在），
一个表空间可以给 n 个用户使用。

不理解oracle数据库_实例_用户_表空间之间的关系
可以参考

1. http://www.bejson.com
2. https://www.2cto.com/database/201801/712011.html

备注：该博客仅为学习交流之用，欢迎大家提出意见和建议，不得用于商业用途，如有转载请标明出处，谢谢合作！