由于测试的需要，下午收到一个邮件，要求搭建一个测试环境。使用expdp导数据的时候，将数据量大于1千万的表只导一部分，其他的表全部导出，然后看了一下要导出的表一共有545张，然而有大概30多张表的数据量大于1千万行，表那么都，不可能一张一张的使用expdp去到，那么就按schema去导出，此时就有一个问题：将一个用户下数据量大于1千万行的表要排除，然后另外使用expdp单独导：刚开始的时候以为会很顺利，很简单，但是事实就是遇到了问题，一下是我遇到的问题。
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=scott_exclude.dmp SCHEMAS=scott EXCLUDE=table:\" in \'EMP\',\'DEPT\'\"
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:20:16 2016
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, 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
[oracle@test01 ~]$ expdp scott/oracle directory=d1 dumpfile=scott_exclude.dmp EXCLUDE=TABLE:"IN ('EMP','DEPT')" full=y
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:21:58 2016
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, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
[oracle@test01 ~]$ expdp scott/oracle directory=d1 dumpfile=scott_exclude.dmp SCHEMAS=scott EXCLUDE=TABLE:"IN ('EMP','DEPT')"
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:21:35 2016
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, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=scott_exclude_%U.dmp SCHEMAS=scott EXCLUDE=table:\" in \'EMP\' \,\'DEPT\'\"
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:32:52 2016
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, 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
通过在OMS上查找想过问题，得到了如下一篇文章
How To Resolve The Error ORA-39071 Value For EXCLUDE Is Badly Formed (文档 ID 734324.1)
In this Document
| Symptoms |
| Cause |
| Solution |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.2 to 11.2.0.3 [Release 10.2 to 11.2]Information in this document applies to any platform.
*** Checked for relevance on 05-SEP-2013 ***
SYMPTOMS
While performing the DataPump in an UNIX or Linux environment you may get the following error, if EXCLUDE or INCLUDE parameter is specified in command prompt:
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier
CAUSE
When you perform expdp or impdp with exclude or include parameter in command line, you have to include the double quotes at the beginning and at the end of the exclude/include statement. This is a UNIX shell issue.
SOLUTION
If you are specifying include or exclude parameter in the command prompt then you need to escape the single and double quotes:
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=test.dmp exclude=table:"='DEPT'" directory=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."EMP" 7.812 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.578 KB 5 rows
. . exported "SCOTT"."Z" 5.546 KB 1 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/testcases/scott/test.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:26:19
以上四OMS上的文档信息。
经过多方查找，最后终于可以完成任务，下面是我的测试信息。
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=scott_exclude.dmp schemas=scott logfile=st_t1.log
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 18:52:22 2016
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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=d1 dumpfile=scott_exclude.dmp schemas=scott logfile=scott_exclude.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."T1" 8.570 KB 14 rows
. . exported "SCOTT"."T2" 8.570 KB 14 rows
. . exported "SCOTT"."T3" 8.570 KB 14 rows
. . exported "SCOTT"."T4" 8.570 KB 14 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scott_exclude.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Oct 11 18:52:58 2016 elapsed 0 00:00:35
--使用scott导出该用户下的表，排除一张表（DEPT）
[oracle@test01 ~]$ expdp scott/oracle dumpfile=test.dmp exclude=table:\"=\'DEPT\'\" directory=d1
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:24:10 2016
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, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=test.dmp exclude=table:"='DEPT'" directory=d1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."T1" 8.570 KB 14 rows
. . exported "SCOTT"."T2" 8.570 KB 14 rows
. . exported "SCOTT"."T3" 8.570 KB 14 rows
. . exported "SCOTT"."T4" 8.570 KB 14 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/test.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Oct 11 19:24:50 2016 elapsed 0 00:00:39
--使用system导出一个schema下的所有张表，排除一张表（DEPT）
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=test_%U.dmp schemas=scott exclude=table:\"=\'DEPT\'\" logfile=scott_exclude.log
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:27:25 2016
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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=d1 dumpfile=test_%U.dmp schemas=scott exclude=table:"='DEPT'" logfile=scott_exclude.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."T1" 8.570 KB 14 rows
. . exported "SCOTT"."T2" 8.570 KB 14 rows
. . exported "SCOTT"."T3" 8.570 KB 14 rows
. . exported "SCOTT"."T4" 8.570 KB 14 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/test_01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Oct 11 19:27:46 2016 elapsed 0 00:00:19
--使用system导出一个schema下的所有张表，排除多张表（EMP,DEPT）
[oracle@test01 ~]$ expdp system/oracle directory=d1 dumpfile=scott_exclude_%U.dmp SCHEMAS=scott EXCLUDE=table:\" in \(\'EMP\' ,\'DEPT\'\)\"
Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:33:21 2016
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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=d1 dumpfile=scott_exclude_%U.dmp SCHEMAS=scott EXCLUDE=table:" in ('EMP' ,'DEPT')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."T1" 8.570 KB 14 rows
. . exported "SCOTT"."T2" 8.570 KB 14 rows
. . exported "SCOTT"."T3" 8.570 KB 14 rows
. . exported "SCOTT"."T4" 8.570 KB 14 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scott_exclude_01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Oct 11 19:33:39 2016 elapsed 0 00:00:17
2126
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
