ORA-39071: Value for EXCLUDE is badly formed

在執行排除部份 table 匯出時，發生ORA-39071的錯誤，經查後主要與 Exclude=TABLE 的參數有關，調整了以下三種方式，都會出錯

oracle@ppdb /> N,TABLE:"IN (INVOICE_METADATA,CRT_INV_SAVE_MT,CRT_INV_SAVE_DT)" schemas=MGR 
oracle@ppdb /> N,TABLE:"IN 'INVOICE_METADATA','CRT_INV_SAVE_MT','CRT_INV_SAVE_DT'" schemas=MGR 
oracle@ppdb /> N,TABLE:\"IN \'INVOICE_METADATA\' \'CRT_INV_SAVE_MT\' \'CRT_INV_SAVE_DT\'\" schemas=MGR              

Export: Release 11.2.0.2.0 - Production on Mon Sep 26 15:20:56 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00933: SQL command not properly ended

將EXCLUDE=TABLE:"IN ('INVOICE_METADATA','CRT_INV_SAVE_MT','CRT_INV_SAVE_DT')" 另外寫成Parfile

oracle@ppdb /> cat expdp.par

exclude=STATISTICS,grant,index,constraint,TABLESPACE,PROFILE,ROLE,ROLE_GRANT,DB_LINK,DIRECTORY,SYNONYM,AUDIT,USER,DEFAULT_ROLE,TABLESPACE_QUOTA,RESOURCE_COST,CONTEXT,PROCACT_SYSTEM,JAVA_CLASS,JAVA_RESOURCE,COMMENT,PASSWORD_HISTORY,PROCACT_SCHEMA,POST_TABLE_ACTION,MATERIALIZED_VIEW,MATERIALIZED_VIEW_LOG,TABLE:"IN ('INVOICE_METADATA','CRT_INV_SAVE_MT','CRT_INV_SAVE_DT')"

完成命令如下

expdp system/oracle directory=dmp dumpfile=expdp_mgr_exclude_%U.dmp logfile=expdp_mgr_20220926151000.log cluster=n compression=all parallel=4 parfile=expdp.par schemas=MGR 

更新：以下用法在 AIX 無效

把單引號與雙引號前面帶入跳脫字元，並且table與table名稱不要有空格或逗號分開

因此參數調整 EXCLUDE=TABLE:\"IN \'INVOICE_METADATA\'\'CRT_INV_SAVE_MT\'\'CRT_INV_SAVE_DT\'\"

oracle@ppdb /> N,TABLE:\"IN \'INVOICE_METADATA\'\'CRT_INV_SAVE_MT\'\'CRT_INV_SAVE_DT\'\" schemas=MGR                

Export: Release 11.2.0.2.0 - Production on Mon Sep 26 15:21:28 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** directory=dmp dumpfile=expdp_mgr_exclude_%U.dmp logfile=expdp_mgr_20220926151000.log cluster=n compression=all parallel=4 exclude=STATISTICS,grant,index,constraint,TABLESPACE,PROFILE,ROLE,ROLE_GRANT,DB_LINK,DIRECTORY,SYNONYM,AUDIT,USER,DEFAULT_ROLE,TABLESPACE_QUOTA,RESOURCE_COST,CONTEXT,PROCACT_SYSTEM,JAVA_CLASS,JAVA_RESOURCE,COMMENT,PASSWORD_HISTORY,PROCACT_SCHEMA,POST_TABLE_ACTION,TABLE:"IN 'INVOICE_METADATA''CRT_INV_SAVE_MT''CRT_INV_SAVE_DT'" schemas=MGR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

完成命令如下

expdp system/oracle directory=dmp dumpfile=expdp_mgr_exclude_%U.dmp \
logfile=expdp_mgr_20220926151000.log cluster=n compression=all parallel=4 \
exclude=STATISTICS,grant,index,constraint,TABLESPACE,PROFILE,ROLE,ROLE_GRANT,DB_LINK,DIRECTORY,SYNONYM,AUDIT,USER,DEFAULT_ROLE,TABLESPACE_QUOTA,RESOURCE_COST,CONTEXT,PROCACT_SYSTEM,JAVA_CLASS,JAVA_RESOURCE,COMMENT,PASSWORD_HISTORY,PROCACT_SCHEMA,POST_TABLE_ACTION,TABLE:\"IN \'INVOICE_METADATA\'\'CRT_INV_SAVE_MT\'\'CRT_INV_SAVE_DT\'\" schemas=MGR