expdp中提供了query参数,可以在需要按条件导出表中部分数据时使用,它的使用就像是在select语句中的where条件使用一样。
数据库版本
zx@ORCL>select*fromv$version; BANNER -------------------------------------------------------------------------------- OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction PL/sqlRelease11.2.0.4.0-Production CORE11.2.0.4.0Production TNSforLinux:Version11.2.0.4.0-Production NLSRTLVersion11.2.0.4.0-Production
创建测试表
zx@ORCL>createtablee1(idnumber,namevarchar2(20)); Tablecreated. zx@ORCL>createtablee2(idnumber,birthdaydate); Tablecreated.
插入测试数据
zx@ORCL>insertintoe1selectlevel,lpad(level,20,'*')fromdualconnectbylevel<=100; 100rowscreated. zx@ORCL>commit; Commitcomplete. zx@ORCL>insertintoe2selectlevel,sysdate-50+levelfromdualconnectbylevel<=100; 100rowscreated. zx@ORCL>commit; Commitcomplete.
创建目录
zx@ORCL>createdirectorydiras'/home/oracle/'; Directorycreated. zx@ORCL>host
测试使用query导出
注意:如果query条件在parfile中则不需要用'\'进行转义
[oracle@rhel6~]$expdpzx/zxdirectory=dirdumpfile=e1.dmptables=zx.e1query=zx.e1:\"whereid<=50\" bash:=50":Nosuchfileordirectory Export:Release11.2.0.4.0-ProductiononThuJul2114:23:112016 Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved. Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions Starting"ZX"."SYS_EXPORT_TABLE_01":zx/********directory=dirdumpfile=e1.dmptables=zx.e1query=zx.e1:"whereid<=50" EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:64KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."E1"6.757KB50rows Mastertable"ZX"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforZX.SYS_EXPORT_TABLE_01is: /home/oracle/e1.dmp Job"ZX"."SYS_EXPORT_TABLE_01"successfullycompletedatThuJul2114:23:262016elapsed000:00:11 exit
查询scn号
zx@ORCL>selectdbms_flashback.get_system_change_numberfromdual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2179047 zx@ORCL>selectcount(*)frome1; COUNT(*) ---------- 100
删除部分数据
zx@ORCL>deletefrome1whereid<20; 19rowsdeleted. zx@ORCL>commit; Commitcomplete. zx@ORCL>host
测试query及flashback_scn
[oracle@rhel6~]$expdpzx/zxdirectory=dirdumpfile=e1_1.dmptables=zx.e1query=zx.e1:\"whereid\<=50\"flashback_scn=2179047 Export:Release11.2.0.4.0-ProductiononThuJul2114:25:412016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions Starting"ZX"."SYS_EXPORT_TABLE_01":zx/********directory=dirdumpfile=e1_1.dmptables=zx.e1query=zx.e1:"whereid<=50"flashback_scn=2179047 EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:64KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."E1"6.757KB50rows Mastertable"ZX"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforZX.SYS_EXPORT_TABLE_01is: /home/oracle/e1_1.dmp Job"ZX"."SYS_EXPORT_TABLE_01"successfullycompletedatThuJul2114:25:492016elapsed000:00:06 [oracle@rhel6~]$exit exit
测试复杂query导出
zx@ORCL>selectcount(*)frome1whereidin(selectidfrome2wherebirthday<sysdate); COUNT(*) ---------- 31 [oracle@rhel6~]$expdpzx/zxdirectory=dirdumpfile=e1_2.dmptables=zx.e1query=zx.e1:\"whereidin\(selectidfrome2wherebirthday\<sysdate\)\" Export:Release11.2.0.4.0-ProductiononThuJul2114:31:042016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions Starting"ZX"."SYS_EXPORT_TABLE_01":zx/********directory=dirdumpfile=e1_2.dmptables=zx.e1query=zx.e1:"whereidin(selectidfrome2wherebirthday<sysdate)" EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:64KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."E1"6.242KB31rows Mastertable"ZX"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforZX.SYS_EXPORT_TABLE_01is: /home/oracle/e1_2.dmp Job"ZX"."SYS_EXPORT_TABLE_01"successfullycompletedatThuJul2114:31:122016elapsed000:00:06 [oracle@rhel6~]$exit exit zx@ORCL>host
测试复杂query及flashback_scn导出
[oracle@rhel6~]$expdpzx/zxdirectory=dirdumpfile=e1_3.dmptables=zx.e1query=zx.e1:\"whereidin\(selectidfrome2wherebirthday\<sysdate\)\"flashback_scn=2179047 Export:Release11.2.0.4.0-ProductiononThuJul2114:32:072016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions Starting"ZX"."SYS_EXPORT_TABLE_01":zx/********directory=dirdumpfile=e1_3.dmptables=zx.e1query=zx.e1:"whereidin(selectidfrome2wherebirthday<sysdate)"flashback_scn=2179047 EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:64KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."E1"6.757KB50rows Mastertable"ZX"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforZX.SYS_EXPORT_TABLE_01is: /home/oracle/e1_3.dmp Job"ZX"."SYS_EXPORT_TABLE_01"successfullycompletedatThuJul2114:32:142016elapsed000:00:06 [oracle@rhel6~]$exit exit
删除e2部分数据
zx@ORCL>deletefrome2whereid>25andid<30; 4rowsdeleted. zx@ORCL>commit; Commitcomplete. zx@ORCL>selectcount(*)frome1whereidin(selectidfrome2wherebirthday<sysdate); COUNT(*) ---------- 27
测试query及flashback_scn,结果只是对e1应用flashback_snc,e2没有应用
zx@ORCL>host [oracle@rhel6~]$expdpzx/zxdirectory=dirdumpfile=e1_4.dmptables=zx.e1query=zx.e1:\"whereidin\(selectidfrome2wherebirthday\<sysdate\)\"flashback_scn=2179047 Export:Release11.2.0.4.0-ProductiononThuJul2114:33:552016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions Starting"ZX"."SYS_EXPORT_TABLE_01":zx/********directory=dirdumpfile=e1_4.dmptables=zx.e1query=zx.e1:"whereidin(selectidfrome2wherebirthday<sysdate)"flashback_scn=2179047 EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:64KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."E1"6.648KB46rows Mastertable"ZX"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforZX.SYS_EXPORT_TABLE_01is: /home/oracle/e1_4.dmp Job"ZX"."SYS_EXPORT_TABLE_01"successfullycompletedatThuJul2114:34:032016elapsed000:00:06 [oracle@rhel6~]$exit exit
使e1和e2都应用flashback_scn
zx@ORCL>selectcount(*)frome1whereidin(selectidfrome2asofscn2179047wherebirthday<sysdate); COUNT(*) ---------- 31 zx@ORCL>host [oracle@rhel6~]$expdpzx/zxdirectory=dirdumpfile=e1_5.dmptables=zx.e1query=zx.e1:\"whereidin\(selectidfrome2asofscn2179047wherebirthday\<sysdate\)\"flashback_scn=2179047 Export:Release11.2.0.4.0-ProductiononThuJul2114:39:522016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions Starting"ZX"."SYS_EXPORT_TABLE_01":zx/********directory=dirdumpfile=e1_5.dmptables=zx.e1query=zx.e1:"whereidin(selectidfrome2asofscn2179047wherebirthday<sysdate)"flashback_scn=2179047 EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:64KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."E1"6.757KB50rows Mastertable"ZX"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ******************************************************************************
多个表使用query条件则使用','分开
[oracle@rhel6~]$expdpsystem/123456directory=dumpdumpfile=query.dmptables=zx.abc,zx.abcequery=zx.abc:\"whereid\<4\",zx.abce:\"whereid\<4\" Export:Release11.2.0.4.0-ProductiononFriDec916:13:412016 Copyright(c)1982,DataMiningandRealApplicationTestingoptions FLASHBACKautomaticallyenabledtopreservedatabaseintegrity. Starting"SYSTEM"."SYS_EXPORT_TABLE_01":system/********directory=dumpdumpfile=query.dmptables=zx.abc,zx.abcequery=zx.abc:"whereid<4",zx.abce:"whereid<4" EstimateinprogressusingBLOCKSmethod... ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA TotalestimationusingBLOCKSmethod:384KB ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE ..exported"ZX"."ABC"5.898KB2rows ..exported"ZX"."ABCE"5.898KB2rows Mastertable"SYSTEM"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ****************************************************************************** DumpfilesetforSYSTEM.SYS_EXPORT_TABLE_01is: /home/oracle/query.dmp Job"SYSTEM"."SYS_EXPORT_TABLE_01"successfullycompletedatFriDec916:14:042016elapsed000:00:19