exclude和include參數能夠在使用expdp或impdp是對特定的對象或對象類型進行篩選或過濾。比如因工作的需要導出特定的表或不導出特定
的表、視圖以及存儲過程、索引、約束、授權統計信息等等。下面將給出expdp或impdp使用exclude和include參數的方法和示例。

?

一、exclude/include參數用法：

??? EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]??-->排出特定對象

?? ?INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]??-->包含特定對象

?? ?object_type子句用于指定對象的類型，如table,sequence,view,procedure,package等等
?? ?name_clause子句可以為SQL表達式用于過濾特定的對象名字。它由SQL操作符以及對象名(可使用通配符)來過濾指定對象類型中的特定對象。
?? ?當未指定name_clause而僅僅指定object_type則所有該類型的對象都將被過濾或篩選。多個[object_type]:[name_clause]中間以逗號分割。

?? ?示例：
????? ?expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"

???? ??impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"

?

二、常用的過濾SQL表達式

??? EXCLUDE=SEQUENCE,VIEW??????????????????????????--過濾所有的SEQUENCE,VIEW
??
?? ?EXCLUDE=TABLE:"IN ('EMP','DEPT')"???????????????--過濾表對象EMP,DEPT
??
??? EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')"?--過濾所有的SEQUENCE,VIEW以及表對象EMP,DEPT
??
?? ?EXCLUDE=INDEX:"= 'INDX_NAME'"???????????????????--過濾指定的索引對象INDX_NAME
??
??? INCLUDE=PROCEDURE:"LIKE 'PROC_U%'"??????????????--包含以PROC_U開頭的所有存儲過程(_ 符號代表任意單個字符)
??
??? INCLUDE=TABLE:"> 'E' "??????????????????????????--包含大于字符E的所有表對象

??? 其它常用操作符 NOT IN, NOT LIKE, <, != 等等
? ??直接將過濾操作符封裝到參數文件中，如下面的例子
? ??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?????????????????????????????????????????????????????????????
??
三、命令行下轉義符的處理

Windows平臺:

??? D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
??? ?INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
??
?? ?在Windows平臺下，需要對象雙引號進行轉義，使用轉義符\

Unix平臺:
? ??在未使用parfile文件的情形下，所有的符號都需要進行轉義，包括括號，雙引號，單引號等
??? % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
??? ?INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

?

四、exclude/include常見的錯誤

??任意需要轉義的字符如果未轉義或轉義錯誤，都會產生ORA錯誤。下面給出幾種常見的ORA錯誤。
???
???? 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.

?

五、導出示例
??-->導出不包含sequence，表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\'\)\"

??-->包含大于字符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/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

??

-->僅僅導入表對象TT，如果存在則將其替換
? ??[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/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

??

-->下面是一個實際實際導出過程中的parfile參數,exclude參數用于排出所有包含BAK的表對象,以及一些索引對象,使用了in,like運算符
? ??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

?

?-->下面是一個使用query查詢過濾的情形
??-->該操作導出的schema為scott，表對象emp中deptno=20，以及dept表中deptno=20的記錄被導出，這兩個表中的其余記錄被過濾
??-->該操作除上述過濾以后的所有對象將被一一導出
??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"'

?

總結

以上是生活随笔為你收集整理的expdp impdp中 exclude/include 的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。