expdp中使用include或者exclude匯出資料

expdp中使用include或者exclude匯出資料就是多了一個篩選關鍵詞。

---匯出Scott模式中所有的物件，除了emp表與dept表不匯出來（exclude）：

--匯出策略：
expdp scott/tiger directory=homedir dumpfile=scott_all_tab.dmp 
schemas=scott  exclude=table:\"  in\(\'EMP\',\'DEPT\'\)\"

[oracle@enmo homedir]$ expdp scott/tiger directory=homedir dumpfile=scott_all_tab.dmp schemas=scott  exclude=table:\"  in\(\'EMP\',\'DEPT\'\)\"
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 22:05:56 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=homedir dumpfile=scott_all_tab.dmp schemas=scott exclude=table:" in('EMP','DEPT')" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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"."HISLOADER"                         5.921 KB       3 rows
. . exported "SCOTT"."MYLOADER"                          6.023 KB       7 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."T1"                                19.77 KB     100 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/homedir/scott_all_tab.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 2 22:06:20 2016 elapsed 0 00:00:23
[oracle@enmo homedir]$ 
#匯出完成。

--檢視匯出檔案：
[oracle@enmo homedir]$ ll
total 344
-rw-r--r-- 1 oracle oinstall  16384 Nov  2 20:10 emp.dmp
-rw-r--r-- 1 oracle oinstall   1791 Nov  2 22:06 export.log
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 21:36 myspace.dmp
-rw-r----- 1 oracle oinstall 253952 Nov  2 22:06 scott_all_tab.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:13 scott.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:16 scotts.dmp
[oracle@enmo homedir]$ 

---匯出Scott模式中的物件，只把emp表與dept表匯出來（include）：
--匯出策略：
expdp scott/tiger directory=homedir dumpfile=scott_some_tab.dmp 
schemas=scott  include=table:\"  in\(\'EMP\',\'DEPT\'\)\"

[oracle@enmo homedir]$ expdp scott/tiger directory=homedir dumpfile=scott_some_tab.dmp schemas=scott  include=table:\"  in\(\'EMP\',\'DEPT\'\)\"
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 22:10:28 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=homedir dumpfile=scott_some_tab.dmp schemas=scott include=table:" in('EMP','DEPT')" 
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/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"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/homedir/scott_some_tab.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 2 22:10:36 2016 elapsed 0 00:00:08
[oracle@enmo homedir]$ 
#匯出完成。

--檢視匯出檔案：
[oracle@enmo homedir]$ ls
emp.dmp  export.log  myspace.dmp  scott_all_tab.dmp  scott.dmp  scotts.dmp  scott_some_tab.dmp
[oracle@enmo homedir]$ ll
total 572
-rw-r--r-- 1 oracle oinstall  16384 Nov  2 20:10 emp.dmp
-rw-r--r-- 1 oracle oinstall   1511 Nov  2 22:10 export.log
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 21:36 myspace.dmp
-rw-r----- 1 oracle oinstall 253952 Nov  2 22:06 scott_all_tab.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:13 scott.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:16 scotts.dmp
-rw-r----- 1 oracle oinstall 229376 Nov  2 22:10 scott_some_tab.dmp
[oracle@enmo homedir]$ 

兩個關鍵詞當中，exclude就是排除選項中的物件，include就是選擇只包括選項中的物件。