概要：
因项目需要，通常需要将生产库下的部分数据抽取并恢复到测试库上
本文主要介绍数据泵导入导出的几种情况以及错误处理
案例环境：
rhel-server-6.5-x86_64
oracle 11.2.0.4.0
一、数据泵导出
a、按用户导出
b、按表导出
b1、全表导出
b2、按查询条件导出表
c、按表空间导出
二、数据泵导入
a、导入到具体用户
a1、按用户导入,不改变schema
a2、按用户导入,改变schema
b、导入表
b1、导入表,不改变schema
b2、导入表,改变schema
c、导入表空间
源数据库：
1、首先查看数据泵目录
SQL> set lines 200
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS SUBDIR /u01/app/oracle/product/11204/db/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11204/db/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11204/db/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11204/db/demo/schema/product_media/
SYS DATA_FILE_DIR /u01/app/oracle/product/11204/db/demo/schema/sales_history/
SYS XMLDIR /u01/app/oracle/product/11204/db/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11204/db/ccr/hosts/localhost.localdomain/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11204/db/ccr/state
9 rows selected.
SQL>
此处，我们数据泵目录选用DATA_PUMP_DIR ，其对应的目录路径为 /u01/app/oracle/admin/orcl/dpdump/ 当然此处也可以新建数据泵的directory，命令如下：
SQL> create directory dump_test as'/dump/test'; Directory created.
2、赋权限（数据泵导入导出时的执行用户 对上面选用的数据泵目录要有读写权限）
SQL> grant read,write on directory DATA_PUMP_DIR to hr; Grant succeeded.
3、数据泵导出
3.1>按用户导出
a>首先查看需要导出的用户所在的表空间，以及该用户下的表在初始化时占用表空间的大小（换算为MB）此处以HR用户为例：
SQL> SELECT ds.tablespace_name,
2 SUM(ds.initial_extent) / 1024 / 1024 as initial_extent
3 FROM DBA_SEGMENTS ds
4 WHERE ds.owner IN ( --查看账户状态为open的用户HR
5 SELECT du.username
6 FROM DBA_USERS du
7 WHERE du.account_status = 'OPEN'
8 and du.username = 'HR')
9 GROUP BY ds.tablespace_name
10 ORDER BY initial_extent desc; TABLESPACE_NAME INITIAL_EXTENT
------------------------------ --------------
EXAMPLE 1.5625
USERS .0625 SQL>b>在表空间数量较多的情况下，可以用以下语句查看表在初始化时占用表空间的总大小：
SQL> SELECT SUM(ds.initial_extent) / 1024 / 1024 as initial_extent
2 FROM DBA_SEGMENTS ds
3 WHERE ds.owner IN ( --查看账户状态为open的用户HR
4 SELECT du.username
5 FROM DBA_USERS du
6 WHERE du.account_status = 'OPEN'
7 and du.username = 'HR'); INITIAL_EXTENT
--------------
1.625 SQL>c>查看表空间对应的数据文件大小
SQL> set lines 200
SQL> col FILE_NAME for a50
SQL> col TOTAL_SPACE for a20
SQL> select tablespace_name, file_id, file_name,
2 round(bytes/(1024*1024),0)||' MB' total_space,AUTOEXTENSIBLE
3 from dba_data_files
4 order by tablespace_name; TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE AUT
------------------------------ ---------- -------------------------------------------------- -------------------- ---
EXAMPLE 5 /oradata/orcl/example01.dbf 313 MB YES
SYSAUX 2 /oradata/orcl/sysaux01.dbf 520 MB YES
SYSTEM 1 /oradata/orcl/system01.dbf 750 MB YES
TEST_TBS 6 /oradata/orcl/test_tbs.dbf 30 MB YES
UNDOTBS1 3 /oradata/orcl/undotbs01.dbf 90 MB YES
USERS 4 /oradata/orcl/users01.dbf 5 MB YES 6 rows selected. SQL>上面a、b、c三个步骤查到的结果，在目标库规划存储空间大小和建表空间初始大小时作为参考条件。
d> expdp按用户导出（schemas=hr）
$ expdp hr/hr directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=hr.log schemas=hr结果如下：
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 21:38:06 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=hr.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jul 3 21:39:19 2016 elapsed 0 00:01:09
3.2>按表导出
3.2.1>全表导出
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=tab.log tables=hr.employees,hr.departments结果如下：
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 21:51:45 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=tab.log tables=hr.employees,hr.departments
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/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tab.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 3 21:52:05 2016 elapsed 0 00:00:17
3.2.2>按查询条件导出表
$ expdp hr/hr directory=DATA_PUMP_DIR dumpfile=condition.dmp logfile=condition.log tables=hr.employees query=\' WHERE DEPARTMENT_ID\=\10\'结果如下：
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 22:06:09 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=DATA_PUMP_DIR dumpfile=condition.dmp logfile=condition.log tables=hr.employees query=' WHERE DEPARTMENT_ID=10'
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 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/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 9.320 KB 1 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/condition.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 3 22:06:25 2016 elapsed 0 00:00:14
3.4>按表空间导出
以example表空间为例
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tablespace.log TABLESPACES=example;结果如下：
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 22:14:26 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLESPACE_01": hr/******** directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tablespace.log TABLESPACES=example
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 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/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "HR"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tablespace.dmp
Job "HR"."SYS_EXPORT_TABLESPACE_01" successfully completed at Sun Jul 3 22:15:04 2016 elapsed 0 00:00:36
4、将源数据库出的文件传到目标数据库
4.1>首先查看目标端据库的数据泵目录
SQL> set lines 200
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS XMLDIR /u01/app/oracle/product/11204/db/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11204/db/ccr/hosts/ggs2/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/hankey/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11204/db/ccr/state SQL>
4.2>在源端，通过scp命令或者其他工具传送文件到目标端，此处使用scp命令
$ pwd
/u01/app/oracle/admin/orcl/dpdump
$ ls
condition.dmp condition.log export.log full.dmp full.log hr.dmp hr.log tab.dmp tablespace.dmp tablespace.log tab.log
$ scp * 10.10.10.10:/u01/app/oracle/admin/hankey/dpdump/
The authenticity of host '10.10.10.10 (10.10.10.10)' can't be established.
RSA key fingerprint is 42:41:f4:75:62:6d:17:2c:e8:79:c1:05:97:82:41:7b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.10' (RSA) to the list of known hosts.
oracle@10.10.10.10's password:
condition.dmp 100% 176KB 176.0KB/s 00:00
condition.log 100% 1613 1.6KB/s 00:00
export.log 100% 2056 2.0KB/s 00:00
full.dmp 100% 151MB 21.6MB/s 00:07
full.log 100% 105KB 104.5KB/s 00:00
hr.dmp 100% 488KB 488.0KB/s 00:00
hr.log 100% 2325 2.3KB/s 00:00
tab.dmp 100% 224KB 224.0KB/s 00:00
tablespace.dmp 100% 404KB 404.0KB/s 00:00
tablespace.log 100% 2079 2.0KB/s 00:00
tab.log 100% 1658 1.6KB/s 00:00
$
目标端(实例名为hankey)
5、进入目标端的数据泵目录，切换用户到root，查看文件并修改权限和目标端数据库一致
$ su - root
Password:
# cd /u01/app/oracle/admin/hankey/dpdump
# chown oracle:oinstall *
# ls -la
total 156580
drwxr-x---. 2 oracle oinstall 4096 Jul 4 01:00 .
drwxr-x---. 5 oracle oinstall 4096 Jun 19 20:26 ..
-rw-r-----. 1 oracle oinstall 180224 Jul 4 01:00 condition.dmp
-rw-r--r--. 1 oracle oinstall 1613 Jul 4 01:00 condition.log
-rw-r-----. 1 oracle oinstall 116 Jun 19 20:39 dp.log
-rw-r--r--. 1 oracle oinstall 2056 Jul 4 01:00 export.log
-rw-r-----. 1 oracle oinstall 158691328 Jul 4 01:00 full.dmp
-rw-r--r--. 1 oracle oinstall 107013 Jul 4 01:00 full.log
-rw-r-----. 1 oracle oinstall 499712 Jul 4 01:00 hr.dmp
-rw-r--r--. 1 oracle oinstall 2325 Jul 4 01:00 hr.log
-rw-r-----. 1 oracle oinstall 229376 Jul 4 01:00 tab.dmp
-rw-r-----. 1 oracle oinstall 413696 Jul 4 01:00 tablespace.dmp
-rw-r--r--. 1 oracle oinstall 2079 Jul 4 01:00 tablespace.log
-rw-r--r--. 1 oracle oinstall 1658 Jul 4 01:00 tab.log
#
6、查看需要目标数据库存在的表空间
SQL> select name from v$tablespace; NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
参考步骤3.1对比源数据库和目标数据库表空间差异，发现，目标端无EXAMPLE，所以在目标端创建EXAMPL 表空间 ，
参考源数据库估算，这里初始大小设置为300M，允许扩展数据文件，在需要扩展时，则分配给数据文件的下一个磁盘空间量的大小为50M
SQL> create tablespace EXAMPLE
2 datafile '/oradata/hankey/example01.dbf' size 300M
3 autoextend on next 50M; Tablespace created. SQL>
附：若创建不合适，需要删除表空间的话，执行以下语句，然后重新创建即可。
SQL> drop tablespace example including contents and datafiles cascade constraints;
7、数据泵导入
7.1>导入到具体用户
7.1.1>按用户导入,不改变schema
对应步骤3.1按用户导出的文件
首先创建需要导入的用户hr
SQL> create user hr identified by hr; User created. SQL> grant connect,resource to hr; Grant succeeded.执行导入命令
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr;结果如下：
Import: Release 11.2.0.4.0 - Production on Mon Jul 4 21:00:46 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.992 KB 19 rows
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HR"."LOCATIONS" 8.273 KB 23 rows
. . imported "HR"."REGIONS" 5.476 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."COUNTRIES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."COUNTRIES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."JOBS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."LOCATIONS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."LOCATIONS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE"
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
ORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 15 error(s) at Mon Jul 4 21:00:55 2016 elapsed 0 00:00:07发现有报错，从报错信息来看，是因为赋权限时找不到名为OE的用户，实际此时hr用户的数据已经完全导入，若就希望在执行时候不报错误，那么继续往下走
通过语句查看目标库确实无OE用户
select username from dba_users;所以，创建oe用户:
SQL> create user oe identified by oe; User created. SQL> grant connect,resource to oe; Grant succeeded. SQL>因上述导入报错，但实际hr用户数据已经导入，若要重新完全导入，首先删除hr用户。
SQL> drop user hr cascade;重新创建用户
SQL> create user hr identified by hr; User created. SQL> grant connect,resource to hr; Grant succeeded. SQL>重新执行导入语句：
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr;结果如下：
Import: Release 11.2.0.4.0 - Production on Mon Jul 4 21:43:25 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.992 KB 19 rows
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HR"."LOCATIONS" 8.273 KB 23 rows
. . imported "HR"."REGIONS" 5.476 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Jul 4 21:43:33 2016 elapsed 0 00:00:06导入成功后，可自行连接hr用户验证核对源库和目标库的数据。
7.1.2>按用户导入,改变schema
首先，目标端新建用户，如下，创建名为buzhengjing的用户
SQL> create user buzhengjing identified by buzhengjing; User created.
SQL> grant connect,resource to buzhengjing; Grant succeeded. SQL>执行导入语句，注意REMAP_SCHEMA=hr:buzhengjing，参数中冒号的前半部分为源数据库的SCHEMA，后半部分为目标数据库要导入的SCHEMA，即新建的用户
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imbzj.log REMAP_SCHEMA=hr:buzhengjing执行结果如下
Import: Release 11.2.0.4.0 - Production on Tue Jul 5 22:00:02 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imbzj.log REMAP_SCHEMA=hr:buzhengjing
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BUZHENGJING"."COUNTRIES" 6.367 KB 25 rows
. . imported "BUZHENGJING"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "BUZHENGJING"."EMPLOYEES" 16.80 KB 107 rows
. . imported "BUZHENGJING"."JOBS" 6.992 KB 19 rows
. . imported "BUZHENGJING"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "BUZHENGJING"."LOCATIONS" 8.273 KB 23 rows
. . imported "BUZHENGJING"."REGIONS" 5.476 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 5 22:02:08 2016 elapsed 0 00:00:06
7.2>导入表
用到步骤3.2按表导出的数据文件
7.2.1>导入表,不改变schema
导入表到同名用户下，由于上一步骤已导入数据，所以此处删除hr用户并重新创建。
SQL> drop user hr cascade;
重新创建用户
SQL> create user hr identified by hr; User created. SQL> grant connect,resource to hr; Grant succeeded. SQL>
执行导入命令
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log执行结果：
Import: Release 11.2.0.4.0 - Production on Mon Jul 4 22:16:03 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 6 error(s) at Mon Jul 4 22:16:07 2016 elapsed 0 00:00:03从报错信息看，是导入完数据后，创建外键约束和触发器失败。当然，数据已经完全导入。
SQL> select table_name from user_tables; TABLE_NAME
------------------------------
DEPARTMENTS
EMPLOYEES SQL> select count(*) from DEPARTMENTS; COUNT(*)
----------
27 SQL> select count(*) from EMPLOYEES; COUNT(*)
----------
107 SQL>此错误可以忽略，但对于强迫症来说，哦，不，对于一个处事风格严谨的人来说，不容许有一个错误出现，才叫完美，那么，接着走。
毫无疑问，上一步骤已导入数据，所以继续删除hr用户并重新创建。
SQL> drop user hr cascade;重新创建用户
SQL> create user hr identified by hr; User created. SQL> grant connect,resource to hr; Grant succeeded. SQL>若要不报错，可以在导出命令中加一个参数exclude，过滤掉constraint（约束）、TRIGGER（触发器）、grant（赋权限）
注意，因为导出的单个表，难免会存在和其他未导出的表存在关联的情况，而这种做法并不是将错误无视并跳过，因为这些对数据导入完整性没有影响，都是在导入完成后进行的。我们可以避免在导出过程中报错，然后根据需要手动建立constraint（约束）、TRIGGER（触发器）和grant（赋权限）等操作，而多数情况下，对于单个表，后续并不需要操作这些的。
执行导入命令
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log tables=hr.employees,hr.departments exclude=constraint,trigger,grantImport: Release 11.2.0.4.0 - Production on Tue Jul 5 00:36:29 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tab.dmp tables=hr.employees,hr.departments exclude=constraint,trigger,grant
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jul 5 00:36:33 2016 elapsed 0 00:00:037.2.2>导入表,改变schema
创建一个新的正经的用户zhengjing
SQL> create user zhengjing identified by zhengjing; User created. SQL> grant connect,resource to zhengjing; Grant succeeded. SQL>执行导入命令，注意用参数REMAP_SCHEMA改变schema，参数exclude的使用同上一步骤
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log REMAP_SCHEMA=hr:zhengjing exclude=constraint,trigger,grantImport: Release 11.2.0.4.0 - Production on Tue Jul 5 01:00:13 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tab.dmp REMAP_SCHEMA=hr:zhengjing exclude=constraint,trigger,grant
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZHENGJING"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "ZHENGJING"."EMPLOYEES" 16.80 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 5 01:00:17 2016 elapsed 0 00:00:03
7.3>导入表空间
对应步骤3.4的按表空间导出文件
首先在目标端查看有哪些表在使用example表空间以及其所属用户
SQL> set lines 200
SQL> select dt.owner, dt.table_name, dt.tablespace_name, dt.status
2 from dba_tables dt
3 where lower(dt.tablespace_name) = 'example'; OWNER TABLE_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
BUZHENGJING DEPARTMENTS EXAMPLE VALID
BUZHENGJING REGIONS EXAMPLE VALID
BUZHENGJING LOCATIONS EXAMPLE VALID
BUZHENGJING JOBS EXAMPLE VALID
BUZHENGJING EMPLOYEES EXAMPLE VALID
BUZHENGJING JOB_HISTORY EXAMPLE VALID
HR EMPLOYEES EXAMPLE VALID
HR DEPARTMENTS EXAMPLE VALID
HR JOB_HISTORY EXAMPLE VALID
HR JOBS EXAMPLE VALID
HR LOCATIONS EXAMPLE VALID OWNER TABLE_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
HR REGIONS EXAMPLE VALID
ZHENGJING EMPLOYEES EXAMPLE VALID
ZHENGJING DEPARTMENTS EXAMPLE VALID 14 rows selected. SQL>因之前演示中创建了许多用户，为避免上述导入的数据干扰产生嫌疑，此处删除这几个用户，当然用户下对应的表也将跟着一起被干掉。
SQL> drop user BUZHENGJING cascade;
drop user HR cascade;
drop user ZHENGJING cascade; User dropped. SQL>
User dropped. SQL>
User dropped.源库的example下存在hr用户，因而，目标库对应的hr用户也要存在。
SQL> create user hr identified by hr; User created. SQL> grant connect,resource to hr; Grant succeeded.执行导入命令
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example执行结果如下
Import: Release 11.2.0.4.0 - Production on Tue Jul 5 01:36:23 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.992 KB 19 rows
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HR"."LOCATIONS" 8.273 KB 23 rows
. . imported "HR"."REGIONS" 5.476 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLESPACE_01" completed with 4 error(s) at Tue Jul 5 01:36:28 2016 elapsed 0 00:00:04查看报错信息，得知是由触发器的引起的，需要加上参数exclude=TRIGGER
在执行导入语句之前，清理并重建用户
SQL> drop user hr cascade; User dropped. SQL> create user hr identified by hr; User created. SQL> grant connect,resource to hr;
Grant succeeded.
SQL>执行导入语句
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example exclude=TRIGGER执行结果如下
Import: Release 11.2.0.4.0 - Production on Tue Jul 5 01:44:30 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example exclude=TRIGGER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.992 KB 19 rows
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HR"."LOCATIONS" 8.273 KB 23 rows
. . imported "HR"."REGIONS" 5.476 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at Tue Jul 5 01:44:34 2016 elapsed 0 00:00:04