The use of exclude/include in EXPDP IMPDP

Exclude and include parameters can filter or filter specific objects or object types when using EXPDP or IMPDP. For example, to export a particular table or not to export a specific
tables, views, and stored procedures, indexes, constraints, authorization statistics, and so on. The following methods and examples are given for EXPDP or IMPDP using exclude and include parameters.

1, Exclude/include parameter usage:

Exclude=[object_type]:[name_clause],[object_type]:[name_clause]--> Expel specific objects

Include=[object_type]:[name_clause],[object_type]:[name_clause]--> contains specific objects

The OBJECT_TYPE clause is used to specify the type of object, such as Table,sequence,view,procedure,package, and so on
The Name_clause clause can be used to filter specific object names for SQL expressions. It filters specific objects in the specified object type by the SQL operator and by the name of the object, which can use wildcards.
When Name_clause is not specified and only the object_type is specified, all objects of that type are filtered or filtered. Multiple [Object_type]:[name_clause] are separated by commas.

Example:
EXPDP <other_parameters> Schemas=scott exclude=sequence,table: "In (' EMP ', ' DEPT ')"

IMPDP <other_parameters> Schemas=scott include=package,function,procedure,table: "= ' EMP '"

2, Commonly used to filter the SQL expression

Exclude=sequence,view--Filter all the Sequence,view

Exclude=table: "In (' EMP ', ' DEPT ')"--Filter Table Object emp,dept

Exclude=sequence,view,table: "In (' EMP ', ' DEPT ')"--Filter all sequence,view and Table objects Emp,dept

Exclude=index: "= ' indx_name '"--filters the specified Index object indx_name

Include=procedure: "Like ' proc_u% '"--contains all stored procedures that begin with Proc_u (_ notation represents any single character)

Include=table: "> ' E '"--contains all table objects greater than character E

Other commonly used operators not in, don't like,!=, etc.
Encapsulate the filter operator directly into the parameter file, as in the following example
Parameter File:exp_scott.par

DIRECTORY = Dump_scott
DumpFile = Exp_scott_%u.dmp
LOGFILE = Exp_scott.log
SCHEMAS = Scott
Parallel= 2
EXCLUDE = TABLE: "In (' EMP ', ' DEPT ')"

EXPDP System/manager Parfile=exp.par

==>author:robinson Cheng
==>blog:http://blog.csdn.net/robinson_0612
==>msn:robinson_0612@hotmail.com
==>qq:645746311

3, The command line of the escape character processing

Windows platform:

d:\> EXPDP system/manager directory=my_dir dumpfile=exp_tab.dmp logfile=exp_tab.log
Include=table:\ "in (' EMP ', ' DEPT ') \"

Under Windows platform, object double quotes are required to escape, using the escape character \

UNIX Platform:
In the case where the Parfile file is not used, all symbols need to be escaped, including parentheses, double quotes, single quotes, and so on.
% EXPDP system/manager directory=my_dir dumpfile=exp_tab.dmp logfile=exp_tab.log Schemas=scott
Include=table:\ "in \ ' emp\ ', \ ' dep\ '"

4. Common mistakes in Exclude/include

Any character that needs to be escaped generates a ORA error if the error is not escaped or escaped. Several common Ora errors are given below.

Ora-39001:invalid argument value
Ora-39071:value for INCLUDE is badly formed.
Ora-00936:missing expression

Ora-39001:invalid argument value
Ora-39071:value for EXCLUDE is badly formed.
ORA-00904: "DEPT": Invalid identifier

Ora-39001:invalid argument value
Ora-39041:filter "INCLUDE" either identifies all object types or no object types.

Ora-39001:invalid argument value
Ora-39041:filter "EXCLUDE" either identifies all object types or no object types

Ora-39001:invalid argument value
Ora-39038:object path "USER" isn't supported for TABLE jobs.

5. Examples of production

--> export does not contain sequence, all other objects of table emp,dept
[Oracle@orasrv scott]$ expdp scott/tiger directory=dump_scott dumpfile=fliter_1.dmp log=fliter_1.log schemas=scott \
> exclude=sequence,table:\ "in \ ' emp\ ', \ ' dept\ '"

--> contains all table objects that are greater than the character T
[Oracle@orasrv ~]$ expdp scott/tiger directory=dump_scott dumpfile=tmp.dmp logfile=tmp.log ' include=table:\ ' \>\ ' \"
...........
Estimate in progress using BLOCKS method ...
Processing Object Type Schema_export/table/table_data
Total estimation using BLOCKS method:18.06 MB
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/statistics/table_statistics
. . Exported "SCOTT". Tb_parl "7.020 MB 72598 rows
. . Exported "SCOTT". TT "7.009 MB 72493 rows
. . Exported "SCOTT". XP ":" P2 "5.398 KB 1 rows
Master table "SCOTT". " Sys_export_schema_01 "Successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT. SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dump/scott/tmp.dmp
Job "SCOTT". " Sys_export_schema_01 "successfully completed at 14:24:55

--> only imports the Table object TT and replaces it if it exists
[Oracle@orasrv scott]$ impdp scott/tiger Directory=dump_scott dumpfile=tmp.dmp \
> include=table:\ "=\ ' tt\ '" table_exists_action=replace
.............
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
Master table "SCOTT". " Sys_import_full_01 "Successfully loaded/unloaded
Starting "SCOTT". Sys_import_full_01 ": scott/******** Directory=dump_scott dumpfile=tmp.dmp Logfile=tmp_imp.log
Include=table: "= ' TT '" table_exists_action=replace
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/table_data
. . Imported "SCOTT". TT "7.009 MB 72493 rows
Processing Object Type Schema_export/table/statistics/table_statistics
Job "SCOTT". " Sys_import_full_01 "successfully completed at 14:59:32

--> The following is a parfile parameter in the actual actual export process, the exclude parameter is used to expel all table objects containing BAK, and some index objects, using the in,like operator
Userid=goex_admin
Directory=db_dump_dir
Dumpfile=sybo2_full_%u.dmp
Logfile=sybo2_full.log
Exclude=table: "Like '%bak% '", Index: "In (' Pk_acc_pos_cash_pl_tbl_arc_11 ', ' pk_acc_pos_cash_tbl_arc_11 ',
' Pk_acc_pos_stock_arch_tbl_11 ', ' i_acc_pos_stock_tbl_arc_dt ', ' I_acc_pos_cash_pl_arch_tbl_dt ',
Table: "In (' Acc_pos_stock_tbl_arc ')"
filesize=5368709120
Schemas=goex_admin

 --> Below is a case where query filtering is used
  --> the schema that the operation exports is Scott, deptno=20 in the Table object EMP, and deptno= in the Dept table 20 of the records are exported, and the remaining records in the two tables are filtered
  --> all objects after this filter will be exported by one by one
&NBSP;&NBSP;EXPDP Scott/tiger dumpfile= Scott_filter.dmp logfile=scott_filter.log directory=db_dump_dir schemas=scott \
  > query=scott.emp : ' Where deptno=20 ', scott.dept: ' Where deptno=20 ' '

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch