【EXPDP】使用EXPDP工具的 EXCLUDE選項過濾掉不關心的資料庫物件

kingsql發表於2014-08-14
使用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 --

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28389881/viewspace-1251235/,如需轉載,請註明出處,否則將追究法律責任。

相關文章