Consider following case for wildcard search in Oracle Datapump export.

If your database contains thousands of tables and you wish to backup only selected tables then probably you will use TABLES datapump parameter by supplying multiple schema.table name in comma separated format. This will be feasible if tables count within 10 or 20.

In this case you can use wildcard search ( % – Percent ) in TABLES datapump parameter.

 

Consider following example, in this we are exporting all tables starting with EMP.

[oracle@oracle ~]$ expdp system/manager tables=scott.EMP% directory=data_pump_bkup dumpfile=emp_wildcard_export.dmp logfile=emp_wildcard.log
Export: Release 11.2.0.1.0 – Production on Thu Mar 6 07:45:18 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** tables=scott.EMP% directory=data_pump_bkup dumpfile=emp_wildcard_export.dmp logfile=emp_wildcard.log

Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “SCOTT”.”EMPTEST” 1.754 MB 262144 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/data_pump_bkup/emp_wildcard_export.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 07:45:33
—x—

 

// We can also export all tables starting with EMP from multiple schemas by using comma separated values, consider following hands-on.

Into this we are exporting tables starting with EMP from SCOTT as well as TEST schema, by using comma separated values as follow:

[oracle@oracle ~]$ expdp system/manager tables=scott.EMP%,test.EMP% directory=data_pump_bkup dumpfile=emp_wildcard_schemas.dmp logfile=emp_wildcard_schemas.log

Export: Release 11.2.0.1.0 – Production on Thu Mar 6 08:32:52 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** tables=scott.EMP%,test.EMP% directory=data_pump_bkup dumpfile=emp_wildcard_schemas.dmp logfile=emp_wildcard_schemas.log

Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.125 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “SCOTT”.”EMPTEST” 1.754 MB 262144 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “TEST”.”EMPLOYEE” 8.578 KB 14 rows
. . exported “TEST”.”EMP” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/data_pump_bkup/emp_wildcard_schemas.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 08:33:08

************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.