The use of exclude/include in EXPDP IMPDP

The exclude and include parameters are capable of filtering or filtering on specific objects or object types when using EXPDP or IMPDP. such as the need to export a specific table for work or not to export a specific
tables, views, and stored procedures, indexes, constraints, authorization statistics, and so on. The methods and examples that EXPDP or IMPDP use the Exclude and include parameters are given below.

First, exclude/include parameter usage:

Exclude=[object_type]:[name_clause],[object_type]:[name_clause]-- draining 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, etc.
The Name_clause clause can be used to filter a specific object name for an SQL expression. It filters specific objects in the specified object type by SQL operators and object names, which can be used with wildcards.
When Name_clause is not specified and only 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 '"

Second, commonly used filter SQL expressionExclude=sequence,view--Filter all the Sequence,view

Exclude=table: "In (' EMP ', ' DEPT ')"--Filter Table object emp,dept
  
    exclude=sequence,view,table : "In (' EMP ', ' DEPT ')"  --filters all Sequence,view and Table objects emp,dept
  
    exclude=index: "= ' indx_name '"                     --filters the specified Index object Indx_name
&NBSP;&NBSP
    include=procedure: "Like ' proc_u% '"                --contains all stored procedures beginning with Proc_u (the _ symbol represents any single character)
&NBSP;&NBSP
    include=table: "> ' E '"                            < Span style= "color: #006600;" >--all table objects that contain greater than character e

Other commonly used operators aren't in, don't like, <,! = etc.
The filter operator is encapsulated directly in 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
==&GT;MSN:[email protected]
==>qq:645746311

iii. handling of command-line escape characters

Windows platform:

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

Under the Windows platform, you need to escape the object double quotes, using the escape character \

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

Iv. Common mistakes in Exclude/include

Any character that needs to be escaped will produce 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" is not supported for TABLE jobs.

v. Examples of samples produced by the guide
--Export all other objects that do not contain sequence, table emp,dept
[Email protected] 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 greater than the character T
[Email protected] ~]$ EXPDP scott/tiger directory=dump_scott dumpfile=tmp.dmp logfile=tmp.log include=table:\ "\>\" T\ ' \ '
...........
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

--Just import the Table object TT and replace it if it exists
[Email protected] scott]$ IMPDP scott/tiger directory=dump_scott dumpfile=tmp.dmp logfile=tmp_imp.log \
> 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 an actual actual export process of the Parfile parameter, the exclude parameter is used to drain all the table objects containing Bak, as well as some indexed 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

-And here's a scenario that uses query queries to filter
The action exports the schema for Scott, the Table object emp in Deptno=20, and the deptno=20 record in the Dept table is exported, and the remaining records in the two tables are filtered
All objects except for the above filter will be exported by one by one
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 ' '

http://blog.csdn.net/liqfyiyi/article/details/7248911

The use of exclude/include in EXPDP IMPDP