Oracle 导出、导入详解（expdp、impdp、exp、imp）

1 概述

1.1 expdp、impdp 和 exp、imp 区别

expdp（export dump）：导出。 读取数据库并将结果集写入到 “导出转储文件（Export Dump File）” 的二进制文件中
impdp（import dump）：导入。读取上述二进制文件并写入到数据库

1.2 使用建议

• 建议将命令写在一行。避免因换行导致部分命令未执行
• 建议在 Windows 运行窗口执行。

2 expdp、impdp

-- 通过以下命令，可查询 expdp 支持的参数，impdp 同理
expdp help=y

2.1 前提：创建 directory 对象

-- 1.先看 directory 对象是否存在
select * from dba_directories t where t.directory_name = 'MYDIR';

-- 2.若不存在，则创建
create directory mydir as 'D:\mydir';

授权（若非 system 用户，则需要，如 scott）：

-- 授予 create 权限
grant create any directory to scott;
-- 授予 read，write 权限
grant read, write on directory mydir to scott;

-- 查看拥有 directory 相关权限的用户
select * from dba_sys_privs t where t.privilege like '%DIRECTORY%';
-- 查询拥有 read，write 相关权限的用户
select * from dba_tab_privs t where t.privilege in ('READ', 'WRITE');

示例：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as system@orcl

SQL> create directory mydir as 'D:\mydir';
Directory created

SQL> grant read, write on directory mydir to scott;
Grant succeeded

SQL> select * from dba_directories t where t.directory_name = 'MYDIR';
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------------------- 
SYS                            MYDIR                          D:\mydir

SQL> 

2.2 导出 expdp

-- 实际开发中，只保留一个空格、不换行，此处仅为了 "显示差异"
expdp 用户名/密码@数据库    目录            转储文件（二进制）         导出模式           是否开启日志
expdp scott/scott@orcl   directory=mydir dumpfile=tables.dmp      tables=emp,dept   logfile=tables.log
expdp scott/scott@orcl   directory=mydir dumpfile=schemas.dmp     schemas=scott     logfile=schemas.log
expdp scott/scott@orcl   directory=mydir dumpfile=tablespaces.dmp tablespaces=users logfile=tablespaces.log
expdp system/system@orcl directory=mydir dumpfile=full.dmp        full=Y            nologfile=Y

例1：导出 scott 下的表 emp

-- 其中 mydir、emp.dmp、emp.log 为自定义名称
expdp scott/scott@orcl directory=mydir dumpfile=emp.dmp tables=emp logfile=emp.log

-- 导出多张表
expdp scott/scott@orcl directory=mydir dumpfile=emp.dmp tables=emp,dept logfile=emp.log

例2：导出 scott 下的表 emp 中符合条件的记录

expdp scott/scott@orcl directory=mydir dumpfile=emp.dmp tables=emp query='emp:"WHERE deptno=10 AND sal>2000"' logfile=emp.log

命令行截图：

物理文件截图：

2.3 导入 impdp

例1：导入 scott 下的表 emp

-- 一般格式
impdp scott/scott@orcl directory=mydir dumpfile=emp.dmp tables=emp logfile=emp.log

-- 导入，追加
impdp scott/scott@orcl directory=mydir dumpfile=emp.dmp tables=emp table_exists_action=APPEND

3 exp、imp

-- 通过以下命令，查询看 exp 支持的参数，imp 同理
exp help=y

3.1 导出 exp

-- 1.全库导出
exp 用户名/密码 file=文件路径 full=Y

-- 2.按用户导出
exp 用户名/密码 file=文件路径 owner=用户

-- 3.按表导出
exp 用户名/密码 file=文件路径 tables=(表1, 表2, 表n)

示例1：导出 scott 下的表 emp 和 dept

set name=demo_%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%
exp scott/tiger@orcl  tables=(emp, dept)  file=D:\Demo备份\%name%.dmp statistics=none

注：以上可保存为 .bat 文件，双击运行

3.2 导入 imp

• 导入 imp 与 导出 emp 命令基本一致

-- 导入表 emp 和 dept
imp soctt/tiger@orcl tables=(emp, dept) file=D:\Demo备份\备份.dmp

4 扩展

4.1 Windows 运行窗口

• 快捷键 Win + r 打开运行窗口，并输入 cmd

cls：清屏命令

4.2 expdp 不是内部或外部命令

• 报错截图：
  
• 解决办法：配置环境变量
  • ① 找到 Oracle 客户端 bin 目录 的路径，如：C:\orac1e\product\12.1.0\dbhome_1\bin
  • ② 在 “系统变量” 中选中 “Path” 变量，添加

4.3 将视图中的数据导出

• 无法直接从 视图（view） 中导出数据，需先将数据插入 临时表 中，再进行导入操作！