[原创]expdp的query语法

这个挺麻烦 试了好多次才试出来
一个是用普通的直接在命令行输入参数一个是通过用parfile方式
具体可以参考Export/Import DataPump Parameter QUERY - How to Specify a Query [ID 277010.1]

1 expdp userid=xx/xx@kpi directory=dump_dir dumpfile=MOD_MR_STATDATA_KQI_20112111.dmp TABLES=MOD_MR_STATDATA_KQI QUERY=MOD_MR_STATDATA_KQI:\"WHERE msg_time >= TO_DATE(\'20110609 10:00:00\',\'yyyymmdd hh24:mi:ss\') AND msg_time < TO_DATE(\'20110609 13:00:00\',\'yyyymmdd hh24:mi:ss\')\" exclude=statistics parallel=4;


windows下:双引号一定要escaped
expdp userid=xx/xx@kpi directory=DUMP_DIR dumpfile=MOD_MR_STATDATA_KQI_2011d2d171111.dmp TABLES=MOD_MR_STATDATA_KQI
QUERY=MOD_MR_STATDATA_KQI:\"WHERE msg_time >= TO_DATE('20110609 10:00:00','yyyym
mdd hh24:mi:ss') AND msg_time < TO_DATE('20110609 13:00:00','yyyymmdd hh24:mi:ss
')\" exclude=statistics

linux下:所有特殊子都否要escaped,貌似冒号不算

expdp xx/xx directory=DATA_PUMP_DIR dumpfile=ABIS_CALL_CDR_RMP_201110704.dmp TABLES=ABIS_CALL_CDR_RMP QUERY=ABIS_CALL_CDR_RMP:\"WHERE start_time \>= TO_DATE\(\'20110609 10:00:00\',\'yyyymmdd hh24:mi:ss\'\) AND start_time \< TO_DATE\(\'20110609 13:00:00\',\'yyyymmdd hh24:mi:ss\'\)\" exclude=statistics


=================================================================
语句拼接:
windows:
select
' expdp xx/xx directory=DATA_PUMP_DIR dumpfile=ABIS_CALL_CDR_RMP_'||'20110704'||'.dmp TABLES=ABIS_CALL_CDR_RMP '||
'QUERY=ABIS_CALL_CDR_RMP:'||'\"WHERE start_time >= TO_DATE(''20110609 10:00:00'',''yyyymmdd hh24:mi:ss'') AND start_time < TO_DATE(''20110609 13:00:00'',''yyyymmdd hh24:mi:ss'')\" exclude=statistics parallel=4'
 FROM user_part_tables WHERE TABLE_NAME='ABIS_CALL_CDR_RMP';

linux:
 
 select
' expdp xx/xx directory=DATA_PUMP_DIR dumpfile=ABIS_CALL_CDR_RMP_'||'20110704'||'.dmp TABLES=ABIS_CALL_CDR_RMP '||
'QUERY=ABIS_CALL_CDR_RMP:'||'\"WHERE start_time \>= TO_DATE\(\''20110609 10:00:00\'',\''yyyymmdd hh24:mi:ss\''\) AND start_time \< TO_DATE\(\''20110609 13:00:00\'',\''yyyymmdd hh24:mi:ss\''\)\" exclude=statistics parallel=4'
 FROM user_part_tables WHERE TABLE_NAME='ABIS_CALL_CDR_RMP'

==============================================================



2 parfile 方式
c:\1.txt
userid=xx/xx
directory=ORACLECLRDIR
dumpfile=MOD_MR_STATDATA_KQI_2011.dmp
TABLES=MOD_MR_STATDATA_KQI 
QUERY=MOD_MR_STATDATA_KQI:"WHERE msg_time >= TO_DATE(\'20110609 10:00:00\',\'yyyymmdd hh24:mi:ss\') AND msg_time < TO_DATE(\'20110609 13:00:00\',\'yyyymmdd hh24:mi:ss\')"


expdp parfile=c:\1.txt
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/184303/viewspace-701319/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/184303/viewspace-701319/