【EXPDP】使用EXPDP工具的 EXCLUDE選項過濾掉不關心的資料庫物件
使用EXPDP邏輯備份工具的EXCLUDE選項可以指定那類資料庫物件不被匯出，EXPDP工具的前身EXP如果想要完成同樣的任務非常的困難。我們以排除部分表為例看一下這個選項帶給我們的便利。
如果在命令列中完成備份，特殊字元的轉義需要特別注意（我這裡使用的是Linux作業系統）。
1.EXPDP幫助中的描述資訊
ora10g@secDB /expdp$ expdp -help
……
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
……
2.建立directory資料庫物件，並將讀寫許可權授予sec使用者
sys@ora10g> create or replace directory dump_dir as '/expdp';
Directory created.
sys@ora10g> grant read,write on directory dump_dir to sec;
Grant succeeded.
3.確認作業系統資訊
ora10g@secDB /expdp$ uname -a
Linux secDB 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux
4.在sec使用者下建立幾張表用於後續的測試
建立三張表T1、T2和T3，每張表中初始化一條資料
sec@ora10g> create table t1 (x int);
sec@ora10g> insert into t1 values (1);
sec@ora10g> create table t2 (x int);
sec@ora10g> insert into t2 values (2);
sec@ora10g> create table t3 (x int);
sec@ora10g> insert into t3 values (3);
sec@ora10g> commit;
5.為了與後面的比較，先全使用者匯出
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 9:59:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T1" 4.914 KB 1 rows
. . exported "SEC"."T2" 4.914 KB 1 rows
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:59:32
6.排除T1表進行備份
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:\"IN\(\'T1\'\)\"
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:02:03
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1')"
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/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T2" 4.914 KB 1 rows
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:02:10
排除表T1後T2和T3表被成功匯出。
7.排除多張表進行備份
以排除表T1和T2兩張表為例進行演示
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:\"IN\(\'T1\',\'T2\'\)\"
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:03:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1','T2')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:03:24
排除表T1和T2後T3表被成功匯出。
注意在Linux的bash下特殊字元的轉義處理
8.使用PARFILE引數規避不同作業系統中特殊字元的轉義
為了規避不同作業系統上特殊字元轉義帶來的麻煩，我們可以使用PARFILE引數規避一下這個難題。
ora10g@secDB /expdp$ vi sec.par
userid=sec/sec
directory=dump_dir
dumpfile=sec.dmp
logfile=sec.log
EXCLUDE=TABLE:"IN('T1','T2')"
~
~
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp parfile=sec.par
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:10:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": parfile=sec.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:10:35
在完成特殊條件匯出時，推薦將需要的所有引數統一寫到引數檔案中。
9.小結
EXPDP工具與EXP相比不僅僅是效率上的提升，更重要的是功能上的增強。
本文中以EXPDP的EXCLUDE選項為例展示了此工具的便捷之處，善用之。
Good luck.
secooler
10.03.08
-- The End --
如果在命令列中完成備份，特殊字元的轉義需要特別注意（我這裡使用的是Linux作業系統）。
1.EXPDP幫助中的描述資訊
ora10g@secDB /expdp$ expdp -help
……
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
……
2.建立directory資料庫物件，並將讀寫許可權授予sec使用者
sys@ora10g> create or replace directory dump_dir as '/expdp';
Directory created.
sys@ora10g> grant read,write on directory dump_dir to sec;
Grant succeeded.
3.確認作業系統資訊
ora10g@secDB /expdp$ uname -a
Linux secDB 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux
4.在sec使用者下建立幾張表用於後續的測試
建立三張表T1、T2和T3，每張表中初始化一條資料
sec@ora10g> create table t1 (x int);
sec@ora10g> insert into t1 values (1);
sec@ora10g> create table t2 (x int);
sec@ora10g> insert into t2 values (2);
sec@ora10g> create table t3 (x int);
sec@ora10g> insert into t3 values (3);
sec@ora10g> commit;
5.為了與後面的比較，先全使用者匯出
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 9:59:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T1" 4.914 KB 1 rows
. . exported "SEC"."T2" 4.914 KB 1 rows
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:59:32
6.排除T1表進行備份
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:\"IN\(\'T1\'\)\"
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:02:03
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1')"
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/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T2" 4.914 KB 1 rows
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:02:10
排除表T1後T2和T3表被成功匯出。
7.排除多張表進行備份
以排除表T1和T2兩張表為例進行演示
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:\"IN\(\'T1\',\'T2\'\)\"
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:03:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1','T2')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:03:24
排除表T1和T2後T3表被成功匯出。
注意在Linux的bash下特殊字元的轉義處理
8.使用PARFILE引數規避不同作業系統中特殊字元的轉義
為了規避不同作業系統上特殊字元轉義帶來的麻煩，我們可以使用PARFILE引數規避一下這個難題。
ora10g@secDB /expdp$ vi sec.par
userid=sec/sec
directory=dump_dir
dumpfile=sec.dmp
logfile=sec.log
EXCLUDE=TABLE:"IN('T1','T2')"
~
~
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp parfile=sec.par
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:10:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_01": parfile=sec.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:10:35
在完成特殊條件匯出時，推薦將需要的所有引數統一寫到引數檔案中。
9.小結
EXPDP工具與EXP相比不僅僅是效率上的提升，更重要的是功能上的增強。
本文中以EXPDP的EXCLUDE選項為例展示了此工具的便捷之處，善用之。
Good luck.
secooler
10.03.08
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1251235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用EXPDP工具的 EXCLUDE選項過濾掉不關心的資料庫物件資料庫物件
- expdp中使用include或者exclude匯出資料
- 【expdp】10g資料泵expdp工具選項詳解及應用示例
- 關於 Oracle 10g EXPDP 的 EXCLUDE 引數Oracle 10g
- 資料泵 EXPDP 匯出工具的使用
- expdp exclude/include 命令出錯
- EXPDP／IMPDP工具的使用
- Expdp,impdp工具的使用
- oracle資料庫的impdp，expdpOracle資料庫
- 使用資料泵工具expdp工具匯出資料
- 使用expdp、impdp遷移資料庫資料庫
- 單例項資料庫expdp遷移到RAC庫單例資料庫
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- 【EXPDP】使用expdp的QUERY引數限定備份資料的範圍
- 【故障處理】通過重建資料庫物件解決因EXPDP/IMPDP工具損壞無法使用問題資料庫物件
- 【EXPDP】不使用DIRECTORY引數完成expdp資料備份
- expdp exclude大量表可以使用子查詢實現
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- EXPDP 和 IMPDP 資料泵的使用_1
- EXPDP 和 IMPDP 資料泵的使用_2
- EXPDP資料泵使用方法
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- 【EXPDP】使用EXPDP備份資料時預估大小——ESTIMATE引數
- expdp 使用QUERY 匯出部分資料。
- expdp匯出表的部分資料
- 通過dblink，資料泵expdp遠端跨版本導庫
- 【EXP】資料庫只讀模式下如何使用exp和expdp資料庫模式
- expdp impdp Data Pump（資料泵）使用解析
- Oracle 10g expdp/impdp的CONTENT選項體驗Oracle 10g
- expdp/impdp操作例項
- expdp時不能匯出sys使用者下的物件！物件
- 使用impdp network link 跳過expdp直接匯入資料
- 使用crontab和expdp實現資料庫定期邏輯備份資料庫
- expdp 匯出特定物件物件
- Oracle Expdp/Impdp 進行資料遷移的 幾點注意事項Oracle
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- expdp/impdp的原理及使用（轉）
- ORACLE expdp匯出遠端庫指定使用者資料到本地資料庫Oracle資料庫