Datapump Export with INCLUDE Parameter to Specify Table Partition Fails with ORA-39071, ORA-920 (Doc ID 2109170.1)

Last updated on FEBRUARY 26, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Trying to use Datapump Expdp to export a table partition using an INCLUDE parameter fails with errors like:

Export: Release 11.2.0.3.0 - Production on Tue Feb 16 15:37:32 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39071: Value for INCLUDE is badly formed.
ORA-00920: invalid relational operator

 
The relevant part of the expdp command used to specify the objects to export is like:

...
QUERY = SAL_SALES_ORDER_ITEM_OPTION":WHERE sales_order_id IN (SELECT so.sales_order_id FROM SAL_SALES_ORDER PARTITION(SO_PART_1Q11) so)"
QUERY = SAL_SALES_ORDER_ITEM_REPL:"WHERE sales_order_id IN (SELECT so.sales_order_id FROM SAL_SALES_ORDER PARTITION(SO_PART_1Q11) so)"
QUERY = SAL_SALES_ORDER_ITEM_RMG:"WHERE sales_order_id IN (SELECT so.sales_order_id FROM SAL_SALES_ORDER PARTITION(SO_PART_1Q11) so)"
QUERY = SAL_SALES_ORDER_NOTES:"WHERE sales_order_id IN (SELECT so.sales_order_id FROM SAL_SALES_ORDER PARTITION(SO_PART_1Q11) so)"
INCLUDE=TABLE:"IN ('SAL_SALES_ORDER_LINE_ITEM', 'SAL_SALES_ORDER_ACTIVITY', 'SAL_SALES_ORDER_ADTL_INFO', 'SAL_SALES_ORDER_COMM', 'SAL_SALES_ORDER_CONTACT', 'SAL_SALES_ORDER_HOLD_ACTION', 'SAL_SALES_ORDER_ITEM_BOM', 'SAL_SALES_ORDER_ITEM_CHARGE', 'SAL_SALES_ORDER_ITEM_CHOICE', 'SAL_SALES_ORDER_ITEM_CONST', 'SAL_SALES_ORDER_ITEM_OPTION', 'SAL_SALES_ORDER_ITEM_REPL', 'SAL_SALES_ORDER_ITEM_RMG', 'SAL_SALES_ORDER_NOTES')"
INCLUDE=TABLE:SAL_SALES_ORDER:SO_PART_1Q11

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms