oracle - 使用 "expdp"在 Oracle 中导出序列

Question

我正在使用以下命令将我的序列导出到 Oracle 中的转储文件:

expdp user/pwd DIRECTORY=user_exp DUMPFILE=morder.dmp  include=sequence:HR.EMPLOYEES 

其中 EMPLOYEES 是我的序列名称。但是，我收到此错误:

ORA-39001 invalid argument value
ORA-39071 Value for INCLUDE is badly formed
ORA-00920 invalid relational operator

有人可以指导一下吗？我做错了什么？

Answer 1

对象名称子句has to be enclosed in double-quotes, and has to have a relational operator :

The name_clause is optional. It allows fine-grained selection of specific objects within an object type. It is a SQL expression used as a filter on the object names of the type. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. The name_clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT). It must be separated from the object type with a colon and enclosed in double quotation marks, because single quotation marks are required to delimit the name strings.

但它也不能包含模式名称；它必须是一个对象名称。如果您以 HR 用户身份连接到 expdp，那么这是默认设置，您可以执行以下操作:

expdp hr/pwd DIRECTORY=user_exp DUMPFILE=morder.dmp include=sequence:"= 'EMPLOYEES'"

如果您以不同的特权用户身份连接，则需要包含 schemas 子句，否则它将无法找到对象:

expdp system/pwd DIRECTORY=user_exp DUMPFILE=morder.dmp schemas=hr include=sequence:"= 'EMPLOYEES'"

根据您的操作系统，您可能需要转义各种内容:

Depending on your operating system, the use of quotation marks when you specify a value for this parameter may also require that you use escape characters. Oracle recommends that you place this parameter in a parameter file, which can reduce the number of escape characters that might otherwise be needed on the command line. See "Use of Quotation Marks On the Data Pump Command Line".

在 Linux/bash 上，include 子句结束为:

... include=sequence:\"= \'EMPLOYEES\'\" 

双引号和单引号都转义了。从之前的问题来看，您可能在 Windows 上，我只需要转义双引号:

... include=sequence:\"= 'EMPLOYEES'\" 

最后，EMPLOYEES 看起来像一个表名；你可能真的想要EMPLOYEES_SEQ:

... include=sequence:\"= 'EMPLOYEES_SEQ'\"