Oracle 11g expdp中query参数的使用

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 ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE11.2.0.4.0ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL 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 <= 100;100 rows created.linuxidc@ORCL>commit;Commit complete.linuxidc@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level <= 100;100 rows created.linuxidc@ORCL>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 id<=50"bash: =50": No such file or directoryExport: Release 11.2.0.4.0 - Production on Thu Jul 21 14:23:11 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1" 6.757 KB 50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:23:26 2016 elapsed 0 00:00:11exit
查询scn号linuxidc@ORCL>select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 2179047linuxidc@ORCL>select count（*） from e1; COUNT（*）---------- 100
删除部分数据linuxidc@ORCL>delete from e1 where id<20;19 rows deleted.linuxidc@ORCL>commit;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<=50" flashback_scn=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:25:41 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:"where id<=50" flashback_scn=2179047Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1" 6.757 KB 50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_1.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:25:49 2016 elapsed 0 00:00:06[oracle@rhel6 ~]$ exitexit测试复杂query导出linuxidc@ORCL>select count（*） from e1 where id in（ select id from e2 where birthday<sysdate）; COUNT（*）----------31[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 where birthday<sysdate）"Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:31:04 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 where birthday<sysdate）"Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1" 6.242 KB 31 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_2.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:31:12 2016 elapsed 0 00:00:06[oracle@rhel6 ~]$ exitexitlinuxidc@ORCL>host
测试复杂query及flashback_scn导出[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 where birthday<sysdate）" flashback_scn=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:32:07 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 where birthday<sysdate）" flashback_scn=2179047Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1" 6.757 KB 50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_3.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:32:14 2016 elapsed 0 00:00:06[oracle@rhel6 ~]$ exitexit
删除e2部分数据linuxidc@ORCL>delete from e2 where id>25 and id<30;4 rows deleted.linuxidc@ORCL>commit;Commit complete.linuxidc@ORCL>select count（*） from e1 where id in（ select id from e2 where birthday<sysdate）; COUNT（*）----------27
测试query及flashback_scn，结果只是对e1应用flashback_snc，e2没有应用linuxidc@ORCL>host[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 where birthday<sysdate）" flashback_scn=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:33:55 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 where birthday<sysdate）" flashback_scn=2179047Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1" 6.648 KB 46 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for ZX.SYS_EXPORT_TABLE_01 is: /home/oracle/e1_4.dmpJob "ZX"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 21 14:34:03 2016 elapsed 0 00:00:06[oracle@rhel6 ~]$ exitexit
使e1和e2都应用flashback_scnlinuxidc@ORCL>select count（*） from e1 where id in（ select id from e2 as of scn 2179047 where birthday<sysdate）; COUNT（*）----------31linuxidc@ORCL>host[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 as of scn 2179047 where birthday<sysdate）" flashback_scn=2179047Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:39:52 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "ZX"."SYS_EXPORT_TABLE_01": zx/******** directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:"where id in （ select id from e2 as of scn2179047 where birthday<sysdate）" flashback_scn=2179047Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."E1" 6.757 KB 50 rowsMaster table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************多个表使用query条件则使用","分开[oracle@rhel6 ~]$ expdp system/123456 directory=dump dumpfile=query.dmp tables=zx.abc,zx.abce query=zx.abc:"where id < 4",zx.abce:"where id < 4"Export: Release 11.2.0.4.0 - Production on Fri Dec 9 16:13:41 2016Copyright （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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dump dumpfile=query.dmp tables=zx.abc,zx.abce query=zx.abc:"where id < 4",zx.abce:"where id < 4"Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 384 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "ZX"."ABC" 5.898 KB 2 rows. . exported "ZX"."ABCE" 5.898 KB 2 rowsMaster table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /home/oracle/query.dmpJob "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 9 16:14:04 2016 elapsed 0 00:00:19更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址