如何从 Oracle 数据泵导出中排除某些表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43787736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to exclude certain tables from Oracle datapump export
提问by sasquartch
I have a large number of user schemas in my Oracle database. I wish to export the table data for these schemas but exclude any tables that are named with the second character an underscore.
我的 Oracle 数据库中有大量用户模式。我希望导出这些模式的表数据，但排除任何以第二个字符为下划线命名的表。
For example I wish to export tables TPI_SUMMARY, DFXRRTand RTAFFbut exclude C_NAMES, G_BEARSetc. Basically just want to exclude anything with an underscore as the second character
例如，我想导出表TPI_SUMMARY，DFXRRT并RTAFF但不包括C_NAMES，G_BEARS等等，基本上只是想用下划线作为第二个字符排除任何东西
I am using Oracle data pump:
我正在使用 Oracle 数据泵：
expdp system/xxxxx@string parfile=parfile.par
The parfilecontains :
该parfile包含：
INCLUDE=TABLE:"IN(select table_name from dba_tables where table_name not like '_\_%')"
EXCLUDE=SCHEMA:"='SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS'"
DIRECTORY=paul_test
DUMPFILE=infrep_temp1.dmp
FULL=Y
LOGFILE=Daily_Backup_infrep_temp1.log
I just cannot get this to work. I thinkthat the EXCLUDEbit is OK but the INCLUDEquery is wrong. The DIRECTORY, DUMPFILEand LOGFILEare all OK.
我只是无法让它发挥作用。我认为这个EXCLUDE位没问题，但INCLUDE查询是错误的。的DIRECTORY，DUMPFILE并且LOGFILE都是确定。
I get the following:
我得到以下信息：
Export: Release 11.2.0.4.0 - Production on Thu May 4 16:41:48 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00933: SQL command not properly ended
Any ideas? I'm really struggling to get this to work.
有任何想法吗？我真的很难让它发挥作用。
回答by Alex Poole
The error refers to EXCLUDE, not INCLUDE:
错误是指EXCLUDE，而不是INCLUDE：
ORA-39071: Value for EXCLUDE is badly formed
ORA-39071: Value for EXCLUDE is badly formed
... and it is indeed that which is wrong. You're trying to use equality with multiple values; you just need to use an IN()again:
……这确实是错误的。您正在尝试对多个值使用相等性；你只需要IN()再次使用：
EXCLUDE=SCHEMA:"IN('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS')"
But as it says in the documentation:
但正如文档中所说：
The
EXCLUDEandINCLUDEparameters are mutually exclusive.
在
EXCLUDE和INCLUDE参数是互斥的。
You are allowed to use two EXCLUDEclauses though, so you can negate the first INCLUDE:
你可以使用两个EXCLUDE子句，所以你可以否定第一个INCLUDE：
EXCLUDE=TABLE:"NOT IN(select table_name from dba_tables where table_name not like '_\_%')"
EXCLUDE=SCHEMA:"IN('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS')"
or as that's now a double-negative:
或者因为现在是双重否定：
EXCLUDE=TABLE:"IN(select table_name from dba_tables where table_name like '_\_%')"
EXCLUDE=SCHEMA:"IN('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS')"
But that likedoesn't match as you expect; you need to add an escapeclause, and in this context you need to escape the backslash by doubling it up:
但这like并不符合您的预期；您需要添加一个escape子句，在这种情况下，您需要通过将反斜杠加倍来转义反斜杠：
EXCLUDE=TABLE:"IN(select table_name from dba_tables where table_name like '_\_%' escape '\')"
or you could use substr()instead:
或者你可以使用substr()：
EXCLUDE=TABLE:"IN(select table_name from dba_tables where substr(table_name, 2, 1) = '_')"
You could also, if you have a list of schemas you dowant, use two INCLUDEclauses instead - which might be simpler that listing all of the built-in schemas, which can vary.
如果您有想要的模式列表，也可以改用两个INCLUDE子句 - 这可能比列出所有内置模式更简单，这些模式可能会有所不同。
