Oracle 11g expdp中提供了query参数，可以在需要按条件导出表中部分数据时使用，它的使用就像是在select语句中的where条件使用一样。
数据库版本
linuxidc@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE11.2.0.4.0Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
创建测试表
linuxidc@ORCL>create table e1 (id number,name varchar2(20));
Table created.
linuxidc@ORCL>create table e2 (id number,birthday date);
Table created.
插入测试数据
linuxidc@ORCL>insert into e1 select level,lpad(level,20,'*') from dual connect by level commit;
Commit complete.
linuxidc@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level commit;
Commit complete.
创建目录
linuxidc@ORCL>create directory dir as '/home/oracle/';
Directory created.
linuxidc@ORCL>host
测试使用query导出
注意：如果query条件在parfile中则不需要用''进行转义
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:"where idselect dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
2179047
linuxidc@ORCL>select count(*) from e1;
COUNT(*)
----------
100
删除部分数据
linuxidc@ORCL>delete from e1 where idcommit;
Commit complete.
linuxidc@ORCL>host
测试query及flashback_scn
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:"where id