oracle expdp导出之query转义问题

背景

需要从一个分区表中拉取一部分数据到另外一个库，实现的方式有很多，这里采用expdp的query功能来实现，基于日期时间过滤时，发过过虑策略不生效。

遇到的问题

[oracle@testoracle ~]$ expdp test/sgtest2016 directory=impdp_dir dumpfile=qxfx_ssqx.dmp tables=(‘tt’) QUERY='tt:“WHERE sjsj<to_date(‘2021-01-01 00:00:00’,‘yyyy-mm-dd HH24:mi:ss’)”'

Export: Release 11.2.0.4.0 - Production on Wed Apr 7 09:21:51 2021

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “test”.“SYS_EXPORT_TABLE_04”: test/******** directory=impdp_dir dumpfile=qxfx_ssqx.dmp tables=(tt) QUERY=tt:"WHERE sjsj<to_date(2021-01-01 00:00:00,yyyy-mm-dd HH24:mi:ss)"
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 44.68 GB

shu

解决方式

[oracle@testoracle ~]$ cat para.par
dumpfile=qxfx_ssqx02.dmp tables=(‘tt’) QUERY=“WHERE sjsj>to_date(‘2021-01-01 00:00:00’,‘yyyy-mm-dd HH24:mi:ss’)”
[oracle@testoracle ~]$ expdp test/sgtest2016 directory=impdp_dir PARFILE=para.par

Export: Release 11.2.0.4.0 - Production on Wed Apr 7 16:59:34 2021

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “test”.“SYS_EXPORT_TABLE_04”: test/******** directory=impdp_dir PARFILE=para.par
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 44.68 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “test”.“tt”:“P01” 11.39 KB 0 rows
. . exported “test”.“tt”:“P02” 5.175 GB 29310006 rows