oracle按照表条件expdp导出数据

编写导出脚本

1. 编写参数文件

如要导出的表的查询条件如下：

select * from mf_acct_dtl where acct_date>'2018-12-31' and acct_date<'2020-01-01'

select * from mf_sub_gen_led where acct_date>'2018-12-31' and acct_date<'2020-01-01'

select * from hs_acct_dtl where acct_date>'2018-12-31' and acct_date<'2020-01-01'

select * from hs_sub_gen_led where acct_date>'2018-12-31' and acct_date<'2020-01-01'

select * from fin_acct_dtl where acct_date>'2018-12-31' and acct_date<'2020-01-01'

select * from fin_sub_gen_led where acct_date>'2018-12-31' and acct_date<'2020-01-01'

参数文件 exp.par 内容如下：

DUMPFILE=20200226.dmp

DIRECTORY=DATA_PUMP_DIR

JOB_NAME=JOB02

TABLES=

(

MF_ACCT_DTL,

MF_SUB_GEN_LED,

HS_ACCT_DTL,

HS_SUB_GEN_LED,

FIN_ACCT_DTL,

FIN_SUB_GEN_LED

)

QUERY=

(

MF_ACCT_DTL:"where acct_date>'2018-12-31' and acct_date<'2020-01-01'",

MF_SUB_GEN_LED:"where acct_date>'2018-12-31' and acct_date<'2020-01-01'",

HS_ACCT_DTL:"where acct_date>'2018-12-31' and acct_date<'2020-01-01'",

HS_SUB_GEN_LED:"where acct_date>'2018-12-31' and acct_date<'2020-01-01'",

FIN_ACCT_DTL:"where acct_date>'2018-12-31' and acct_date<'2020-01-01'",

FIN_SUB_GEN_LED:"where acct_date>'2018-12-31' and acct_date<'2020-01-01'"

)

2. 编写导出命令

expdp  "'" / as sysdba"'"  parfile=exp.par