就拿EMP表当例子
案例1：要求把deptno=20的数据导出来
语句：
expdp scott/oracle directory=xx dumpfile=2emp.dmp tables=emp query='emp:"WHERE deptno=20"'; ---注意后面这几个"'是"+'

点击(此处)折叠或打开

[oracle@xie xie]$ expdp scott/oracle directory=xx dumpfile=2emp.dmp tables=emp query='emp:"WHERE deptno=20"';
Export: Release 11.2.0.4.0 - Production on Thu Mar 23 14:24:32 2017
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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=xx dumpfile=2emp.dmp tables=emp query=emp:"WHERE deptno=20"
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/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
. . exported "SCOTT"."EMP" 8.195 KB 5 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/xie/2emp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 23 14:24:38 2017 elapsed 0 00:00:06

然后如果要把这些数据导入到emp表里的话，先去删除原表的这几条数据，再去执行以下语句导入：
impdp scott/oracle dumpfile=2emp.dmp directory=xx content=data_only

点击(此处)折叠或打开

[oracle@xie xie]$ impdp scott/oracle dumpfile=2emp.dmp directory=xx content=data_only
Import: Release 11.2.0.4.0 - Production on Thu Mar 23 14:25:59 2017
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 "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/******** dumpfile=2emp.dmp directory=xx content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 8.195 KB 5 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Thu Mar 23 14:26:03 2017 elapsed 0 00:00:03

案例2：要求把ename='KING'的这条数据导出来
如果query里的条件是字符串的话，就要用下面这种方法：
先写出一个参数文件txt.par
[oracle@xie xie]$ cat txt.par
dumpfile=1emp.dmp
directory=xx
tables=(emp)
query=(emp:"where ename='KING'")
然后执行导出语句：
expdp scott/oracle parfile=txt.par

点击(此处)折叠或打开

[oracle@xie xie]$ expdp scott/oracle parfile=txt.par
Export: Release 11.2.0.4.0 - Production on Thu Mar 23 14:12:13 2017
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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** parfile=txt.par
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/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
. . exported "SCOTT"."EMP" 8.031 KB 1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/xie/1emp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 23 14:12:21 2017 elapsed 0 00:00:07

导入方法和案例1的一样

注：如果query里的条件是字符串还用案例1的方法，就会报错
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00904: "KING": invalid identifier
还在探索这个错误的原因，待研究

ITPub博客

ORACLE 使用EXPDP按条件导出

最新文章