Oracle数据迁移expdp/impdp

共计 31015 个字符，预计需要花费 78 分钟才能阅读完成。

本文实验环境：Oracle 11.2.0.4，利用数据库自带的 scott 示例用户进行试验测试。

1. 首先需要创建 Directory

2. 使用 expdp 导出用户数据

2.1 只导出 scott 用户的元数据，且不包含统计信息；
2.2 只导出 scott 用户的数据；
2.3 只导出 scott 用户下的 emp，dept 表及数据；
2.4 只导出 scott 用户下的 emp，dept 表结构；
2.5 导出 scott 用户下所有的内容；
2.6 并行导出 scott 用户下所有的内容；

3. 查询当前用户用到的表空间

特别注意：如果后续要导入的数据库版本低，所有导出命令就需要在后面加一个 version= 指定版本。
例如 11g -> 10g，假设 10g 具体版本为 10.2.0.1，那么就加一个版本的参数version=10.2.0.1。

1. 首先需要创建 Directory

这里目录名字定义为 ”jy”，
若是 windows 平台，对应系统目录为 ”E:\jingyu”;

create or replace directory jy as 'E:\jingyu';

若是 Unix/Linux 平台，对应系统目录为 ”/tmp/jingyu”.

create or replace directory jy as '/tmp/jingyu';

注意：目录在系统上需要真实存在（mkdir -p /tmp/jingyu），且有访问的权限。
drwxr-xr-x. 2 oracle oinstall 4.0K May 22 16:48 jingyu

2. 使用 expdp 导出用户数据

2.1 只导出 scott 用户的元数据，且不包含统计信息；

expdp system directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log

$ expdp system directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 16:57:59 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log 
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
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_meta.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:13 2015 elapsed 0 00:00:11

2.2 只导出 scott 用户的数据；

expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log

$ expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 16:58:47 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_data.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:57 2015 elapsed 0 00:00:07

2.3 只导出 scott 用户下的 emp，dept 表及数据；

这里如果用 scott 用户导出，需要注意 scott 用户对于 directory 的权限问题：需要 dba 用户赋予 scott 用户 read,write 目录的权限。
即：grant read, write on directory jy to scott;

SQL> grant read, write on directory jy to scott;

Grant succeeded.

expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log

$ expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 17:13:55 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /tmp/jingyu/scott_emp_dept.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 22 17:14:04 2015 elapsed 0 00:00:06

2.4 只导出 scott 用户下的 emp，dept 表结构；

expdp scott directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log

$ expdp scott directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 17:14:51 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /tmp/jingyu/scott_emp_dept_meta.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 22 17:15:01 2015 elapsed 0 00:00:07

2.5 导出 scott 用户下所有的内容；

expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log

$ expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 17:15:52 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_all.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 17:16:06 2015 elapsed 0 00:00:11

2.6 并行导出 scott 用户下所有的内容；

expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

$ expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

Export: Release 11.2.0.4.0 - Production on Fri May 22 16:55:13 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
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/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_all01.dmp
  /tmp/jingyu/scott_all02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:56:12 2015 elapsed 0 00:00:54

3. 查询当前用户用到的表空间

select tablespace_name from user_tables union
select tablespace_name from user_tab_partitions union
select tablespace_name from user_indexes union
select tablespace_name from user_ind_partitions;

impdp 导入。

1. 首先需要创建 Directory

2. 创建表空间和用户及赋权

3. 使用 impdp 导入用户数据

3.1 导入 scott 用户的元数据，且不包含统计信息；
3.2 导入 scott 用户的数据；
3.3 导入 scott 用户下的 emp，dept 表及数据；
3.4 导入 scott 用户下的 emp，dept 表结构；
3.5 导入 scott 用户下所有的内容；
3.6 并行导入 scott 用户下所有的内容；

4. 特殊需求

4.1 如果导入环境的用户不同；
4.2 如果导入环境的表空间也不同；

由于我这里的实验环境还是导出的那台主机，所以需要先模拟出一个导入的环境，你实际导入并不需要。

1. 删除 scott 用户；
drop user scott cascade;
2. 删除 users 表空间。
drop tablespace users including contents and datafiles;
可能 users 表空间是默认的数据库表空间，导致删除失败，只需要更改下再执行删除即可。

select a.property_name, a.property_value from database_properties a where a.property_name like '%DEFAULT%';
alter database default tablespace DBS_D_XXX;

这样就模拟出了一个没有 users 表空间和 scott 用户的崭新环境。

1. 首先需要创建 Directory

这里目录名字定义为 ”jy”，
若是 windows 平台，对应系统目录为 ”E:\jingyu”;

create or replace directory jy as 'E:\jingyu';

若是 Unix/Linux 平台，对应系统目录为 ”/tmp/jingyu”.

create or replace directory jy as '/tmp/jingyu';

注意：目录在系统上需要真实存在（mkdir -p /tmp/jingyu），且有访问的权限。
drwxr-xr-x. 2 oracle oinstall 4.0K May 22 16:48 jingyu

2. 创建表空间和用户及赋权

create tablespace users datafile '+data1' size 10M autoextend on maxsize 30G;
create user scott identified by tiger default tablespace users;
grant connect, resource to scott;
grant read, write on directory jy to scott;

3. 使用 impdp 导入用户数据

初始化环境：
初始 1：得到删除当前用户下表的 SQL：select 'drop table'||table_name||'purge;' from user_tables;
初始 2：得到查询当前用户下表的数据量：select 'select count(1) from'||table_name||';' from user_tables;

3.1 导入 scott 用户的元数据，且不包含统计信息；

impdp system directory=jy dumpfile=scott_meta.dmp logfile=impdp_scott_meta.log

$ impdp system directory=jy dumpfile=scott_meta.dmp logfile=impdp_scott_meta.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:36:41 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=jy dumpfile=scott_meta.dmp logfile=impdp_scott_meta.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" 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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue May 26 13:36:49 2015 elapsed 0 00:00:03

3.2 导入 scott 用户的数据；

在 3.1 导入元数据后才可以导入数据。
impdp system directory=jy dumpfile=scott_data.dmp logfile=impdp_scott_data.log

$ impdp system directory=jy dumpfile=scott_data.dmp logfile=impdp_scott_data.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:39:15 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=jy dumpfile=scott_data.dmp logfile=impdp_scott_data.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue May 26 13:39:26 2015 elapsed 0 00:00:07

3.3 只导入 scott 用户下的 emp 表及数据；

这里为了演示导入，先初始化删除 scott 用户下的所有表。

impdp scott directory=jy tables=emp dumpfile=scott_emp_dept.dmp logfile=impdp_scott_emp.log

$ impdp scott directory=jy tables=emp dumpfile=scott_emp_dept.dmp logfile=impdp_scott_emp.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:50:51 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=jy tables=emp dumpfile=scott_emp_dept.dmp logfile=impdp_scott_emp.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 26 13:50:59 2015 elapsed 0 00:00:03

3.4 只导入 scott 用户下的 emp,dept 表结构；

这里为了演示导入，先初始化删除 scott 用户下的所有表。

impdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log

$ impdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 13:54:38 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=jy tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 26 13:54:45 2015 elapsed 0 00:00:03

由于导出就是 emp,dept 两张表，所以也可以不指定 tables，以下两种写法在这里都是可以的：
impdp scott directory=jy dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log

impdp scott directory=jy dumpfile=scott_emp_dept_meta.dmp logfile=impdp_scott_emp_dept_meta.log full=y

3.5 导入 scott 用户下所有的内容；

impdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=impdp_scott_all.log

如果是在 2.4 基础上直接导入，会因为 emp,dept 表已经存在导致导入过程中会由于 table_exists_action 参数的默认选项是 skip，从而跳过 emp，dept 表数据的导入，如下：

$ impdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=impdp_scott_all.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:22:50 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott dumpfile=scott_all.dmp logfile=impdp_scott_all.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" 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
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Tue May 26 14:22:57 2015 elapsed 0 00:00:03

所以这时我们想导入这些数据，可以加参数 table_exists_action，指定想要的选项。
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

这里选择 truncate，即如果表存在，那么处理方式是 truncate 此表后导入文件中包含的数据。
impdp system directory=jy schemas=scott table_exists_action=truncate dumpfile=scott_all.dmp logfile=impdp_scott_all.log

$ impdp system directory=jy schemas=scott table_exists_action=truncate dumpfile=scott_all.dmp logfile=impdp_scott_all.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:26:09 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott table_exists_action=truncate dumpfile=scott_all.dmp logfile=impdp_scott_all.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" 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
Table "SCOTT"."DEPT" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."BONUS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."SALGRADE" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."EMP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:26:17 2015 elapsed 0 00:00:04

注意：如果这里选用 append 选项，那么如果原表有数据，且没有合理的约束条件，则可能导致数据的重复导入，所以，生产环境实际导入过程中一定要弄清楚数据的实际情况才能准确决定如何选用此参数的选项 。
如下所示，SALGRADE 表会出现 5 条重复数据：
impdp system directory=jy schemas=scott table_exists_action=append dumpfile=scott_all.dmp logfile=impdp_scott_all.log

$ impdp system directory=jy schemas=scott table_exists_action=append dumpfile=scott_all.dmp logfile=impdp_scott_all.log

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:28:27 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott table_exists_action=append dumpfile=scott_all.dmp logfile=impdp_scott_all.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" 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
Table "SCOTT"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."BONUS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."SALGRADE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SCOTT"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.BIN$ESfmGQ7ZSsLgU58JqMBQqw==$0) violated
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Tue May 26 14:28:34 2015 elapsed 0 00:00:03

3.6 并行导入 scott 用户下所有的内容；

impdp system directory=jy schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

这里对于已经存在的对象直接 replace 掉。

$ impdp system directory=jy schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:15:37 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" 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 "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:15:46 2015 elapsed 0 00:00:05

4. 特殊需求

特殊需求环境准备：
创建表空间 users2：
create tablespace users2 datafile '+data1' size 10M autoextend on maxsize 30G;
创建用户 scott2：
create user scott2 identified by tiger default tablespace users2;
赋权用户 scott2：
grant connect, resource to scott2;

4.1 如果导入环境的用户不同；


需求：将原 scott 用户的数据导入到现在的 scott2 用户。
impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

$ impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:55:02 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT2" 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 "SCOTT2"."DEPT"                             5.929 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.484 KB      12 rows
. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:55:09 2015 elapsed 0 00:00:04

4.2 如果导入环境的表空间也不同；

需求：将原 users 表空间的对象重定向到 users2 表空间。
impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

$ impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 14:57:20 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT2" 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 "SCOTT2"."DEPT"                             5.929 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.484 KB      12 rows
. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue May 26 14:57:29 2015 elapsed 0 00:00:05

细心的朋友，会发现导入的日志最后都提示有一个错误，往上查发现是报错 ORA-31684 用户已存在，这是因为我们习惯在导入前建立好对应的用户，避免一些其他的权限错误，所以这个错误是可以忽略的。
当然其实如果我们已经建立了对应的表空间，用户也是可以不事先建立的，比如：
我们这里的情景，如果只事先建立 users2 表空间，不建立 scott2 用户，也是可以成功导入且不会有任何报错提示。

$ impdp system directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2

Import: Release 11.2.0.4.0 - Production on Tue May 26 15:03:33 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=jy schemas=scott REMAP_SCHEMA=scott:scott2 REMAP_TABLESPACE=users:users2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=impdp_scott_all.log parallel=2 
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/TABLE_DATA
. . imported "SCOTT2"."DEPT"                             5.929 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.484 KB      12 rows
. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 26 15:03:42 2015 elapsed 0 00:00:05