【Oracle】导入导出（imp/exp/impdp/expdp）

1 概述

在10g之前,传统的导出和导入分别使用EXP工具和IMP工具,从10g开始,不仅保留了原有的EXP和IMP工具,还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项：

1. EXP和IMP是客户端工具程序,它们既可以在客户端使用,也可以在服务端使用。

2. EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用。

3. IMP只适用于EXP导出文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出文件,而不适用于EXP导出文件。

数据泵取只能在服务器端运行，客户端只能用来连接服务器启动导入导出操作

exp和expdp原理对比

1. exp/imp客户端程序，受网络，磁盘的影响; impdp/expdp服务器端程序，只受磁盘的影响

2. 假设文件把文件导出在服务端，exp常规方式是使用SELECT的方式查询数据库中的数据，是需要通过buffer cache并通过sql的语句处理层再转移至exp的导出文件(即要经过sga,pga才传输到客户端，而exp直接路径则是直接从磁盘上将数据转移至exp的导出文件，所以速度更快;而expdp是exp的升级版，相当于exp + direct mode + parallel(可能对导出文件还进行了压缩，这是从导出文件的对比结果发现的，具体不清楚)。所有从总的来说，expdp比exp提供了并行的方式(写入多个导出文件)

impdp的并行机制也让其比imp快很多

2 导入导出

2.1 exp

【交互方式备份emp表】/tmp/emp.dmp

[mingjie.gmj@ecs ~][00]$ exp scott/tigger

Export: Release 12.1.0.2.0 - Production on Sat Jun 15 10:22:22 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > /tmp/emp.dmp

(2)U(sers), or (3)T(ables): (2)U > T

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > emp

. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully with warnings.
[mingjie.gmj@ecs ~][00]$ ll /tmp/emp.dmp
-rw-r--r-- 1 mingjie.gmj users 16384 Jun 15 10:22 /tmp/emp.dmp

【交互方式备份scott用户】/tmp/scott.dmp

[mingjie.gmj@ecs ~][00]$ exp scott/tigger

Export: Release 12.1.0.2.0 - Production on Sat Jun 15 10:25:55 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > /tmp/scott.dmp

(2)U(sers), or (3)T(ables): (2)U > U

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       SALGRADE          5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          TBL01          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          TBL02          1 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[mingjie.gmj@ecs ~][00]$ ll /tmp/scott.dmp
-rw-r--r-- 1 mingjie.gmj users 24576 Jun 15 10:26 /tmp/scott.dmp

【命令行方式备份emp表】

exp scott/tigger tables=emp file=/tmp/emp1.dmp log=/tmp/emp1.log

【命令行方式备份scott用户】

exp scott/tigger  owner=scott file=/tmp/scott1.dmp log=/tmp/scott1.log

【命令行方式emp不带数据】

exp system/ocp tables=scott.emp rows=no file=/tmp/emp2.dmp log=/tmp/emp2.log

【命令行方式emp指定条件】

exp system/ocp tables=scott.emp query=\'where deptno=10\'  file=/tmp/emp3.dmp log=/tmp/emp3.log

【命令行方式导出表空间】

exp system/ocp  tablespaces=users  file=/tmp/test1.dmp log=/tmp/test1.log

2.2 imp

【恢复用户】

[mingjie.gmj@ecs ~][00]$ rlwrap sqlplus / as sysdba

SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by tigger;

User created.

SQL> grant unlimited tablespace to scott;

Grant succeeded.

[mingjie.gmj@ecs ~][00]$ imp system/Server_234 fromuser=scott touser=scott file=/tmp/scott1.dmp log=/tmp/scott1.log

[mingjie.gmj@ecs ~][00]$ rlwrap sqlplus / as sysdba

SQL> grant connect,resource to scott;

Grant succeeded.

SQL> conn scott/tigger;
Connected.
SQL> select * from TBL01;

	 I
----------
	 1

2.3 expdp

查询系统目录

SQL> select * from dba_directories;

OWNER	   DIRECTORY_NAME		  DIRECTORY_PATH								   ORIGIN_CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- -------------
SYS	   ORACLE_HOME			  /											       0
SYS	   ORACLE_BASE			  /											       0
SYS	   OPATCH_LOG_DIR		  /fdisk1/oracle1400/base/dbhome_1/QOpatch						       0
SYS	   OPATCH_SCRIPT_DIR      /fdisk1/oracle1400/base/dbhome_1/QOpatch						       0
SYS	   OPATCH_INST_DIR		  /fdisk1/oracle1400/base/dbhome_1/OPatch						       0
SYS	   DATA_PUMP_DIR		  /home/mingjie.gmj/databases/oracle1400/base/admin/orcl1400/dpdump/			       0
SYS	   XSDDIR                 /fdisk1/oracle1400/base/dbhome_1/rdbms/xml/schema					       0
SYS	   ORACLE_OCM_CONFIG_DIR  /fdisk1/oracle1400/base/dbhome_1/ccr/hosts/iZbp1d4tisi44j6vxze02fZ/state		       0
SYS	   XMLDIR                 /fdisk1/oracle1400/base/dbhome_1/rdbms/xml						       0
SYS	   ORACLE_OCM_CONFIG_DIR2 /fdisk1/oracle1400/base/dbhome_1/ccr/state						       0

10 rows selected.

创建系统目录

create directory depdir as '/tmp/depdir';
grant read,write on directory depdir to scott;

查询系统目录

SQL> select * from dba_directories where DIRECTORY_NAME='DEPDIR';

OWNER	   DIRECTORY_NAME		  DIRECTORY_PATH    ORIGIN_CON_ID
---------- ---------------------- ----------------- -------------
SYS        DEPDIR                 /tmp/depdir       0

导出scott的数据

mkdir /tmp/depdir

expdp scott/tigger DIRECTORY=depdir DUMPFILE=scott.dmp

导出scott用户和emp表数据

expdp scott/tigger DIRECTORY=depdir DUMPFILE=emp.dmp tables=scott.emp;

导出scott用户的emp表结构，不需要数据

expdp scott/tigger DIRECTORY=depdir DUMPFILE=emp1.dmp tables=scott.emp content=METADATA_ONLY PARALLEL=8

导出emp2，后面恢复这个

expdp scott/tigger DIRECTORY=depdir DUMPFILE=scott2.dmp LOGFILE=scott2.log SCHEMAS=scott

[[email protected] ~][00]$ ll /tmp/depdir/
total 1100
-rw-r----- 1 mingjie.gmj users 180224 Jun 15 11:09 emp1.dmp
-rw-r----- 1 mingjie.gmj users 192512 Jun 15 11:07 emp.dmp
-rw-r--r-- 1 mingjie.gmj users   1241 Jun 15 11:09 export.log
-rw-r----- 1 mingjie.gmj users 364544 Jun 15 11:21 scott2.dmp
-rw-r--r-- 1 mingjie.gmj users   2038 Jun 15 11:21 scott2.log
-rw-r----- 1 mingjie.gmj users 364544 Jun 15 11:05 scott.dmp

2.4 impdp

将scott的数据导入scott_fake用户

create user scott_fake identified by tigger;
grant unlimited tablespace to scott_fake;
grant CREATE SESSION to scott_fake;

impdp system/Server_234 DIRECTORY=depdir DUMPFILE=scott2.dmp LOGFILE=scott2.log REMAP_SCHEMA=scott:scott_fake

验证数据

SQL> select TABLE_NAME from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE
TBL01
TBL02

6 rows selected.