Export sequence in Oracle using "expdp"
I am using the following command to export my sequence to a dump file in Oracle:
expdp user/pwd DIRECTORY=user_exp DUMPFILE=morder.dmp include=sequence:HR.EMPLOYEES
where EMPLOYEES is my sequence name. However, I get this error:
ORA-39001 invalid argument value
ORA-39071 Value for INCLUDE is badly formed
ORA-00920 invalid relational operator
Can someone please guide about this? What am I doing wrong?
Answer
The object name clause 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.
But it also can't include a schema name; it just has to be an object name. If you are connecting to expdp as the HR user then that's the default anyway and you can do:
expdp hr/pwd DIRECTORY=user_exp DUMPFILE=morder.dmp include=sequence:"= 'EMPLOYEES'"
If you're connecting as a different, privileged, user you need to include the schemas clause, or it won't be able to find the object:
expdp system/pwd DIRECTORY=user_exp DUMPFILE=morder.dmp schemas=hr include=sequence:"= 'EMPLOYEES'"
Depending on your operating system you may need to escape various things:
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".
On Linux/bash the include clause ends up as:
... include=sequence:\"= \'EMPLOYEES\'\"
with both the double-quotes and single-quotes escaped. From a previous question you may be on Windows, where I only the double-quotes need to be escaped:
... include=sequence:\"= 'EMPLOYEES'\"
Finally, EMPLOYEES looks like a table name; you probably really want EMPLOYEES_SEQ:
... include=sequence:\"= 'EMPLOYEES_SEQ'\"