EXPDP匯出報ORA-31693 ORA-29913 ORA-01861錯誤

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