Use of exclude/include in expdp impdp

The exclude and include parameters can filter or filter specific objects or object types when using expdp or impdp. For example, to export a specific table or not to export a specific table
Tables, views, stored procedures, indexes, constraints, and authorization statistics. The following describes how to use exclude and include parameters for expdp or impdp.

 

1. Exclude/include parameter usage:

Exclude = [object_type]: [name_clause], [object_type]: [name_clause] --> discharge a specific object

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

The object_type clause is used to specify the object type, such as table, sequence, view, procedure, and package.
The name_clause can be used as an SQL expression to filter specific object names. It uses the SQL operator and Object Name (wildcards can be used) to filter specific objects of the specified object type.
If name_clause is not specified and only object_type is specified, all objects of this type will be 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 '"

 

Ii. Commonly used SQL expressions for filtering

Exclude = sequence, View -- filter all sequence, View

Exclude = table: "In ('emp', 'dept')" -- filter table objects EMP, Dept

Exclude = sequence, view, table: "In ('emp', 'dept')" -- filter all sequence, views, and table objects such as EMP and dept.

Exclude = index: "= 'indx _ name'" -- filter the specified index object indx_name

Include = procedure: "like 'proc _ u % '" -- contains all stored procedures starting with proc_u (_ represents any single character)

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

Other common operators not in, not like, <,! = And so on
Directly encapsulate the filter operator in the parameter file, as shown 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. Handling escape characters in the command line

Windows:

D: \> expdp system/manager directory = my_dir dumpfile = exp_tab.dmp logfile = exp_tab.log schemas = Scott
Include = table: \ "in ('emp', 'dept ')\"

On Windows, double quotation marks (") of objects must be escaped. Escape Character \

UNIX platform:
When the parfile file is not used, all symbols must be escaped, including brackets, double quotation marks, and single quotation marks.
% Expdp system/manager directory = my_dir dumpfile = exp_tab.dmp logfile = exp_tab.log schemas = Scott
Include = table: \ "in \ (\ 'emp' \ ', \ 'dep \'\)\"

 

Iv. Common exclude/include errors

Any character to be escaped, if not escaped or escaped, will generate a ora error. Below are several common ora errors.

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. Export example
--> Export all other objects that do not contain sequence, table EMP, and 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 greater than T
[Oracle @ orasrv ~] $ 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
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 import the table object TT. If it exists, replace it
[Oracle @ orasrv 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
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 export process. The exclude parameter is used to discharge all table objects containing Bak and some index objects. The in and like operators are used.
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

 

--> The following is a query filtering scenario.
--> The schema exported by this operation is Scott. The deptno = 20 in the table object EMP and the deptno = 20 in the dept table are exported. The remaining records in the two tables are filtered.
--> All objects filtered out above will be exported 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 "'

 

6. More references

Oracle External table

Use external tables to manage Oracle alarm logs (alaert _ $ Sid. Log)

Cluster table and cluster Table Management (index clustered tables)

Use of Data Pump expdp export tool

Use of Data Pump impdp import tool

Import and Export Oracle Partition Table Data

SQL * loader usage