ORACLE数据泵expdp导出impdp导入

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

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

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

目录

使用场景：

在工作中，涉及到的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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
--创建逻辑路径
create directory EXPNC_DIR as 'E:\oracletablespace\expnc';

--命令行执行 expdp操作

cmd->[导出语法]


  • 1
  • 2
  • 3
  • 4
  • 5

导出语法

需要在命令行中执行

--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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

数据泵导入（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->[导入语法]


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

导入语法

需要在命令行中执行

--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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

实战演练

针对数据库用户表的复制(改变表的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"

2  DATAFILE 'E:\oracletablespace\user'

3  size 500M AUTOEXTEND on next 100M

4  maxsize unlimited logging extent

5  management local segment space management auto;


表空间已创建。


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

在这里插入图片描述
创建users用户，设置表空间，并授权

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

  • 1
  • 2
  • 3
  • 4

在这里插入图片描述

退出并切换到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 行。


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

在这里插入图片描述

导出（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;
授权成功。

  • 1
  • 2
  • 3
  • 4
  • 5

在这里插入图片描述

执行 数据泵（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 行。

  • 1
  • 2
  • 3
  • 4

导入（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;
用户已删除。

  • 1
  • 2
  • 3
  • 4

执行 数据泵（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

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

posted @ 2022-02-23 22:27  yclizq  阅读(893)  评论(0)  编辑  收藏  举报