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, ..)
