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

Last updated on JULY 24, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Jul-2013***

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/tiger 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/tiger 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:
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\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

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