How To Avoid Error ORA-39165 When Wildcard "%" Is Used In Table Mode DataPump Export? (Doc ID 1357951.1)

Last updated on JULY 17, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 23-Jul-2013***

Goal

A DataPump export does not export all the tables when a wildcard character "%" is used with parameter TABLES.

Example:

User1 has 3 tables called TABLE_01, TABLE_02 and TABLE_10. An export executed with the following parameters.

#> expdp system/oracle tables=user1.table_0%, user1.table_1% dumpfile=test001.dmp

failed with the following error:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=user1.table_0%,
user1.table_1% dumpfile=test001.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema USER1 was not found.
ORA-39166: Object TABLE_1% was not found.
ORA-39166: Object TABLE_0% was not found.
ORA-31655: no data or metadata objects selected for job


The same behavior does not happen on 11gR2 where the tables using wildcard are exported without problem.

Solution

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