1 使用如下语句导出数据，报错
[oracle@host_A ftp]$ expdp \'/ as sysdba\' JOB_NAME=xsc0206 directory=FTP_EXP tables=user_A.table_A
QUERY=\"where DATA_DATE \>\= to_date\(\'2023-01-01\'\,\'yyyy-mm-dd\'\)\" dumpfile=table_A_0206.dmp
logfile=table_A_0206.log CLUSTER=N FLASHBACK_SCN=15986516223909
Export: Release 11.2.0.4.0 - Production on Tue Feb 7 14:55:04 2023
Copyright (c) 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."XSC0206": "/******** AS SYSDBA" JOB_NAME=xsc0206 directory=FTP_EXP tables=user_A.table_A QUERY="where DATA_DATE >= to_date('2023-01-01','yyyy-mm-dd')"
dumpfile=table_A_0206.dmp logfile=table_A_0206.log CLUSTER=N FLASHBACK_SCN=15986516223909
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 53.59 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "user_A"."table_A":"TEMP20230130" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01861: literal does not match format string
ORA-31693: Table data object "user_A"."table_A":"TEMP20230131" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01861: literal does not match format string
ORA-31693: Table data object "user_A"."table_A":"TEMP20221106" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01861: literal does not match format string
2 根据报错信息，怀疑同表的字段类型不符，查看表结构
SYS@e200pro1 >desc user_A.table_A
Name Null? Type
-------------------------- -------- --------------------
ORG_WWW NOT NULL VARCHAR2(16)
CWWW_TYPE NOT NULL NUMBER(3)
CWWW_SUBTYPE NOT NULL VARCHAR2(16)
DATA_DATE NOT NULL VARCHAR2(8)
TOTWWW_NUM NOT NULL NUMBER(10)
.....
3 查看过滤条件的时间格式
select data_date from user_A.table_A;
data_date
---------------
20160101
20160101
20160101
20160101
20160101
20160101
20160101
20160101
20160101
20160101
20160101
20160101
20160101
4 修改expdp导出过滤条件，导出正常
[oracle@host_A ftp]$ expdp \'/ as sysdba\' JOB_NAME=xsc0207_1 directory=FTP_EXP tables=user_A.table_A
QUERY=\"where DATA_DATE \>\= \'20230101\'\" dumpfile=table_A_0207.dmp logfile=table_A_0207.log
CLUSTER=N FLASHBACK_SCN=15986516223909
Export: Release 11.2.0.4.0 - Production on Tue Feb 7 15:28:17 2023
Copyright (c) 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."XSC0207_1": "/******** AS SYSDBA" JOB_NAME=xsc0207_1 directory=FTP_EXP tables=user_A.table_A QUERY="where DATA_DATE >= '20230101'"
dumpfile=table_A_0207.dmp logfile=table_A_0207.log CLUSTER=N FLASHBACK_SCN=15986516223909
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 53.59 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "user_A"."table_A":"TEMP20230130" 52.54 MB 566304 rows
. . exported "user_A"."table_A":"TEMP20230131" 52.60 MB 566907 rows
. . exported "user_A"."table_A":"TEMP20221106" 21.19 KB 0 rows
. . exported "user_A"."table_A":"TEMP20221107" 21.19 KB 0 rows