oracle 11g expdp工具的query参数使用

fswdnr

　　实际生产中导数据时可能会需要导部分数据到测试库上，expdp的query参数就可以完成这样的需求。
　　数据库版本
　　zx@ORCL>select * from v$version;
　　BANNER
　　--------------------------------------------------------------------------------

　　Oracle Database 11g Enterprise Edition>
　　PL/SQL>　　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 commit;
　　Commit complete.
　　zx@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level commit;
　　Commit complete.
　　创建目录
　　zx@ORCL>create directory dir as '/home/oracle/';
　　Directory created.
　　zx@ORCL>host
　　测试使用query导出

　　[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:\"where>　　bash: =50": No such file or directory

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

　　Connected to: Oracle Database 11g Enterprise Edition>　　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>　　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>　　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>
　　Export:>　　Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

　　Connected to: Oracle Database 11g Enterprise Edition>　　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>　　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导出

　　zx@ORCL>select count(*) from e1 where>　　COUNT(*)
　　----------
　　31

　　[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:\"where>
　　Export:>　　Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

　　Connected to: Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　Starting "ZX"."SYS_EXPORT_TABLE_01":  zx/******** directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:"where>　　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.242 KB      31 rows
　　Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
　　******************************************************************************
　　Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
　　/home/oracle/e1_2.dmp
　　Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:31:12 2016 elapsed 0 00:00:06
　　[oracle@rhel6 ~]$ exit
　　exit
　　zx@ORCL>host
　　测试复杂query及flashback_scn导出

　　[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:\"where>
　　Export:>　　Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

　　Connected to: Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　Starting "ZX"."SYS_EXPORT_TABLE_01":  zx/******** directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:"where>　　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_3.dmp
　　Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:32:14 2016 elapsed 0 00:00:06
　　[oracle@rhel6 ~]$ exit
　　exit
　　删除e2部分数据

　　zx@ORCL>delete from e2 where>　　4 rows deleted.
　　zx@ORCL>commit;
　　Commit complete.

　　zx@ORCL>select count(*) from e1 where>　　COUNT(*)
　　----------
　　27
　　测试query及flashback_scn，结果只是对e1应用flashback_snc，e2没有应用
　　zx@ORCL>host

　　[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:\"where>
　　Export:>　　Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

　　Connected to: Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　Starting "ZX"."SYS_EXPORT_TABLE_01":  zx/******** directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:"where>　　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.648 KB      46 rows
　　Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
　　******************************************************************************
　　Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
　　/home/oracle/e1_4.dmp
　　Job "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:34:03 2016 elapsed 0 00:00:06
　　[oracle@rhel6 ~]$ exit
　　exit
　　使e1和e2都应用flashback_scn

　　zx@ORCL>select count(*) from e1 where>　　COUNT(*)
　　----------
　　31
　　zx@ORCL>host

　　[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:\"where>
　　Export:>　　Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

　　Connected to: Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　Starting "ZX"."SYS_EXPORT_TABLE_01":  zx/******** directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:"where>　　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
　　******************************************************************************