Oracle 11g expdp中query参数的使用
发布时间:2020-12-12 15:35:28 所属栏目:百科 来源:网络整理
导读:expdp中提供了query参数,可以在需要按条件导出表中部分数据时使用,它的使用就像是在select语句中的where条件使用一样。 数据库版本 zx@ORCLselect*fromv$version;BANNER--------------------------------------------------------------------------------
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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |