Oracle 11g expdp中query参数的使用

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

原文链接：/oracle/211336.html

Oracle 11g expdp中query参数的使用

猜你在找的Oracle相关文章