进行导入导出时报错：ORA-39038

刘泽宇

在进行expdp导出数据时报错，模拟如下：
[oracle@test ~]$ expdp sys/oracle directory=dump_dir exclude=schema:\"in\(\'user1\',\'user2\',\'user3\'\)\" dumpfile=test.dmp
Export: Release 11.2.0.4.0 - 64bit Production on Tuesday, 20 April, 2021 13:39:06
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39038: Object path "SCHEMA" is not supported for SCHEMA jobs.

这个错误有相关的文档说明(文档 ID 1362645.1)
ORA-39001: invalid argument value
ORA-39038: Object path "SCHEMA" is not supported for SCHEMA jobs.

CAUSE
The following DataPump export command line was used:

expdp system/passwd directory=test dumpfile=test.exp logfile=test.log EXCLUDE=SCHEMA:\"in \(\'TEST\', \'SCOTT\'\)\"

If you didn't specify what kind of export you want to run, then the default schema export is taken.
The export above runs a SCHEMA export for user SYSTEM and the parameter EXCLUDE=SCHEMA:"IN ('TEST', 'SCOTT') is not a valid.

SOLUTION
To be able to exclude number of SCHEMAs, the export type have to be FULL.
Add  "FULL=Y" to the command line and rerun the export again:

expdp system/passwd directory=test dumpfile=test.exp logfile=test.log EXCLUDE=SCHEMA:\"in \(\'TEST\', \'SCOTT\'\)\" FULL=Y

再次进行导出
[oracle@test ~]$ expdp sys/oracle directory=dump_dir exclude=schema:\"in\(\'user1\',\'user2\',\'user3\'\)\" dumpfile=test.dmp full=y
Export: Release 11.2.0.4.0 - 64bit Production on Tuesday, 20 April, 2021 14:10:02
Copyright (c) 2003, 2007, Oracle.  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 "SYSTEM"."SYS_EXPORT_FULL_01":  sys/******** directory=dump_dir exclude=schema:"in('user1','user2','user3')" dumpfile=a.dmp full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA