Export sequence in Oracle using "expdp"

Question

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 1

Here is the copy/paste from my environment:

[oracle@testsrv ~]$ expdp uuu/uuu schemas=uuu DIRECTORY=dir1 DUMPFILE=morder3.dmp  include=sequence

Export: Release 12.1.0.2.0 - Production on Mon Nov 16 22:16:59 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "UUU"."SYS_EXPORT_SCHEMA_01":  uuu/******** schemas=uuu DIRECTORY=dir1 DUMPFILE=morder3.dmp include=sequence 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Master table "UUU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for UUU.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/morder3.dmp
Job "UUU"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 16 22:17:03 2015 elapsed 0 00:00:03

[oracle@testsrv ~]$ 

Answer 2

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'\" 

Answer 3

In case you want to export sequence from specific SCHEMA use this:

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

(add SCHEMAS and remove the owner od the sequence)