Datapump Export with INCLUDE Parameter to Specify Table Partition Fails with ORA-39071, ORA-920
(Doc ID 2109170.1)
Last updated on AUGUST 02, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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
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 = <TABLE 12>":WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
QUERY = <TABLE 13>:"WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
QUERY = <TABLE 14>:"WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
QUERY = <TABLE 15>:"WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
INCLUDE=TABLE:"IN ('<TABLE 2>', '<TABLE 3>', '<TABLE 4>', '<TABLE 5>', '<TABLE 6>', '<TABLE 7>', '<TABLE 8>', '<TABLE 9>', '<TABLE 10>', '<TABLE 11>', '<TABLE 12>', '<TABLE 13>', '<TABLE 14>', '<TABLE 15>')"
INCLUDE=TABLE:<Table 1>:<PARTITION NAME>
QUERY = <TABLE 12>":WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
QUERY = <TABLE 13>:"WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
QUERY = <TABLE 14>:"WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
QUERY = <TABLE 15>:"WHERE <Col 1> IN (SELECT <Alias>.<Col 1> FROM <Table 1> PARTITION(<PARTITION NAME>) <Alias>)"
INCLUDE=TABLE:"IN ('<TABLE 2>', '<TABLE 3>', '<TABLE 4>', '<TABLE 5>', '<TABLE 6>', '<TABLE 7>', '<TABLE 8>', '<TABLE 9>', '<TABLE 10>', '<TABLE 11>', '<TABLE 12>', '<TABLE 13>', '<TABLE 14>', '<TABLE 15>')"
INCLUDE=TABLE:<Table 1>:<PARTITION NAME>
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |