expdp中提供了query参数，可以在需要按条件导出表中部分数据时使用，它的使用就像是在select语句中的where条件使用一样。
成都创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于网站建设、成都做网站、台儿网络推广、微信小程序开发、台儿网络营销、台儿企业策划、台儿品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；成都创新互联公司为所有大学生创业者提供台儿建站搭建服务，24小时服务热线：18982081108，官方网址：www.cdcxhl.com
数据库版本
zx@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
创建测试表
zx@ORCL>create table e1 (id number,name varchar2(20)); Table created. zx@ORCL>create table e2 (id number,birthday date); Table created.
插入测试数据
zx@ORCL>insert into e1 select level,lpad(level,20,'*') from dual connect by level <= 100; 100 rows created. zx@ORCL>commit; Commit complete. zx@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level <= 100; 100 rows created. zx@ORCL>commit; Commit complete.
创建目录
zx@ORCL>create directory dir as '/home/oracle/'; Directory created. zx@ORCL>host
测试使用query导出
注意：如果query条件在parfile中则不需要用'\'进行转义
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:\"where id<=50\" bash: =50": No such file or directory Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:23:11 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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:"where id<=50" 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 . . exported "ZX"."E1" 6.757 KB 50 rows Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1.dmp Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:23:26 2016 elapsed 0 00:00:11 exit
查询scn号
zx@ORCL>select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2179047 zx@ORCL>select count(*) from e1; COUNT(*) ---------- 100
删除部分数据
zx@ORCL>delete from e1 where id<20; 19 rows deleted. zx@ORCL>commit; Commit complete. zx@ORCL>host
测试query及flashback_scn
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:\"where id\<=50\" flashback_scn=2179047 Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:25:41 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 "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:"where id<=50" flashback_scn=2179047 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 . . exported "ZX"."E1" 6.757 KB 50 rows Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_1.dmp Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:25:49 2016 elapsed 0 00:00:06 [oracle@rhel6 ~]$ exit exit
测试复杂query导出