【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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫的impdp，expdpOracle資料庫
- [20200620]expdp impdp exclude引數.txt
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- 通過dblink，資料泵expdp遠端跨版本導庫
- 使用impdp,expdp資料泵進入海量資料遷移
- 批量按使用者expdp
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle expdp資料泵遠端匯出Oracle
- 使用Exp和Expdp匯出資料的效能對比與最佳化
- expdp query用法
- 資料泵datapump（expdp/impdp）的parfile用法，避免跳脫字元字元
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- oracle按照表條件expdp匯出資料Oracle
- expdp一個例子
- Java程式呼叫expdp資料泵實現自動邏輯備份Oracle資料庫的方案設計JavaOracle資料庫
- 關於dcat-admin 資料庫過濾查詢資料庫
- 【資料泵】EXPDP匯出表結構(真實案例)
- 基於flashback_scn的expdp匯出
- 3分鐘短文：Laravel的“南天門”，過濾掉七七八八的資料Laravel
- exp和expdp的filesize引數的使用--匯出多個檔案
- expdp在匯出時對資料大小進行評估
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）Oracle
- expdp+compression效能測試
- 正常終止expdp作業
- expdp報錯ORA-39181
- 使用MySQL之過濾資料MySql
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle 11g 透過expdp按日期匯出表Oracle
- Oracle 12.1.0.2 expdp匯出分割槽表資料遇到BUG慢的原因和解決方法Oracle
- Mybatis的<if>標籤的坑，0會被過濾掉？MyBatis
- expdp 邏輯備份指令碼指令碼
- [20180628]expdp與rows=n.txt
- [20200309]expdp 與read only.txt
- expdp 匯出時指定節點
- expdp/impdp變慢 (Doc ID 2469587.1)
- 【Data Pump】expdp/impdp Job基本管理
- DBeaverEE for Mac——資料庫管理的首 選工具Mac資料庫
- ORACLE EXPDP IMPDP 的停止和啟動及監控Oracle