Discover What's Possible
Header

Filtering During Export Operations

Specific to Datapump much metadata filtering capability been provided. It allows selection of specific objects within an object type. It is a SQL expression used as a filter on the type’s object names. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT). The name clause must be separated from the object type with a colon and enclosed in double quotation marks, because single-quotation marks are required to delimit the name strings. For example, you could set EXCLUDE=TABLE:"LIKE 'EMP%'" to exclude all tables whose names start with emp & so on.

In Filtering we have more option as “LIKE” & “NOT LIIKE”, Depends on selectivity we can choose which option will be simpler and easy.

Using NOT-LIKE with EXCLUDE:-

C:\Users\bn2676>expdp system/manager directory=data_pump_dir dumpfile=exp_scott1.dmp logfile=exp_scott1.log schemas=scott EXCLUDE=TABLE:\"NOT LIKE \'%MP\'\"
Export: Release 11.2.0.1.0 - Production on Tue Feb 21 09:15:04 2012
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=data_pump_dir dumpfile=exp_scott1.dmp logfile=exp_scott1.log schemas=scott EXCLUDE=TABLE:"NOT LIKE \'%MP\'
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLE\ADMIN\ORCL\DPDUMP\EXP_SCOTT1.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:15:23

Using LIKE with EXCLUDE:-

C:\Users\bn2676>expdp system/manager directory=data_pump_dir dumpfile=exp_scott2.dmp logfile=exp_scott2.log schemas=scott EXCLUDE=TABLE:\"LIKE \'EMP%\'\"
Export: Release 11.2.0.1.0 - Production on Tue Feb 21 09:16:44 2012
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=data_pump_dir dumpfile=exp_scott2.dmp logfile=exp_scott2.log schemas=scott EXCLUDE=TABLE:"LIKE \'EMP%\'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLE\ADMIN\ORCL\DPDUMP\EXP_SCOTT2.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:17:04

Using LIKE with INCLUDE:-

C:\Users\bn2676>expdp system/manager directory=data_pump_dir dumpfile=exp_scott2.dmp logfile=exp_scott2.log schemas=scott INCLUDE=TABLE:\"LIKE \'%EMP\'\"
Export: Release 11.2.0.1.0 - Production on Fri Mar 2 12:04:22 2012
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** directory=data_pump_dir dumpfile=exp_scott2.dmp logfile=exp_scott2.log schemas=scott INCLUDE=TABLE:"LIKE \'%EMP\'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
  C:\ORACLE\ADMIN\ORCL\DPDUMP\EXP_SCOTT2.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 12:04:29
C:\Users\bn2676>

Using NOT-LIKE with INCLUDE:-

C:\Users\bn2676>expdp system/manager directory=data_pump_dir dumpfile=exp_scott.dmp logfile=exp_scott1.log schemas=scott INCLUDE=TABLE:\"NOT LIKE \'%EMP\'\"
Export: Release 11.2.0.1.0 - Production on Fri Mar 2 12:03:29 2012
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** directory=data_pump_dir dumpfile=exp_scott.dmp logfile=exp_scott1.log schemas=scott INCLUDE=TABLE:"NOT LIKE \'%EMP\'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19.12 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T2"                                15.35 MB   50000 rows
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CKPT":"CKPT1"                          0 KB       0 rows
. . exported "SCOTT"."CKPT":"CKPT2"                          0 KB       0 rows
. . exported "SCOTT"."CKPT":"CKPT3"                          0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
  C:\ORACLE\ADMIN\ORCL\DPDUMP\EXP_SCOTT.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 12:03:40
C:\Users\bn2676>

There are mixed Clauses with Conditions using Exclude/Include options :)
Exclude – Like (Tables, Indexes, ..)
- Not Like (Tables, Indexes, ..)

Include – Like (Tables, Indexes, ..)
- Not Like (Tables, Indexes, ..)

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers:

Paste your AdWords Remarketing code here