My Oracle Support Banner

How To Export Specific Tables That Contain An Underscore In The Name From Data Pump (Doc ID 733745.1)

Last updated on MARCH 08, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Goal

This document is created to explain how to perform an export of specific tables. For example, if you have six tables that begin with the letter C:

SQL> select table_name from user_tables where table_name like 'C%';

TABLE_NAME
------------------------------
CB
CA
CC
C_A
C_B
C_C


The following query will allow the ability to query ONLY the tables with "C_":

SQL> select table_name from user_tables where table_name like '%C\_%'ESCAPE'\';

TABLE_NAME
------------------------------
C_A
C_B
C_C


However, when attempting to export and trying to use the same logic with DataPump

#>expdp scott/<password> dumpfile=data_pump_dir:test.dmp TABLES='\"%C\_%'ESCAPE'\'"' logfile=data_pump_dir:mylog.log

it fails with:

Export: Release 10.2.0.4.0 - Production on Wednesday, 20 August, 2008 7:13:55

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********
dumpfile=data_pump_dir:test.dmp TABLES='"%C\_%'ESCAPE'\'' logfile=data_pump_dir:mylog.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object ESCAPE''' was not found.
ORA-39166: Object %C\_% was not found.
ORA-31655: no data or metadata objects selected for job
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 07:14:13


Attempting to use the wildcard with the export, it will export ALL tables that begin with C:

#> expdp scott/<password> dumpfile=data_pump_dir:test.dmp TABLES='\"C_%\"' logfile=data_pump_dir:mylog.log


Export: Release 10.2.0.4.0 - Production on Wednesday, 20 August, 2008 7:16:45

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********
dumpfile=data_pump_dir:test.dmp TABLES='"C_%"' logfile=data_pump_dir:mylog.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."CA" 7.820 KB 14 rows
. . exported "SCOTT"."CB" 7.820 KB 14 rows
. . exported "SCOTT"."CC" 7.820 KB 14 rows
. . exported "SCOTT"."C_A" 7.820 KB 14 rows
. . exported "SCOTT"."C_B" 7.820 KB 14 rows
. . exported "SCOTT"."C_C" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
<PATH>\TEST.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 07:16:54


So how to force DataPump to export the "C_"  tables only?

Solution

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
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.