oracle----【EXPDP】使用EXPDP工具的 EXCLUDE選項過濾掉不關心的數(shù)據(jù)

1、【EXPDP】使用EXPDP工具的 EXCLUDE選項過濾掉不關心的數(shù)據(jù)庫對象上一篇 / 下一篇 2010-03-08 22:54:51 / 個人分類：備份與恢復查看( 952 )/ 評論( 2 ) / 評分( 5 / 0 )使用EXPDP邏輯備份工具的EXCLUDE選項可以指定那類數(shù)據(jù)庫對象不被導出，EXPDP工具的前身EXP如果想要完成同樣的任務非常的困難。我們以排除部分表為例看一下這個選項帶給我們的便利。如果在命令行中完成備份，特殊字符的轉(zhuǎn)義需要特別注意（我這里使用的是Linux操作系統(tǒng)）。1.EXPDP幫助中的描述信息ora10gsecDB /expdp$ expdp -helpEXC

2、LUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.2.創(chuàng)建directory數(shù)據(jù)庫對象，并將讀寫權(quán)限授予sec用戶sysora10g> create or replace directory dump_dir as '/expdp'Directory created.sysora10g> grant read,write on directory dump_dir to sec;Grant succeeded.3.確認操作系統(tǒng)信息ora10gsecDB /expdp$ uname -aLinux s

3、ecDB 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux4.在sec用戶下創(chuàng)建幾張表用于后續(xù)的測試創(chuàng)建三張表T1、T2和T3，每張表中初始化一條數(shù)據(jù)secora10g> create table t1 (x int);secora10g> insert into t1 values (1);secora10g> create table t2 (x int);secora10g> insert into t2 values (2);secora10g>

4、; create table t3 (x int);secora10g> insert into t3 values (3);secora10g> commit;5.為了與后面的比較，先全用戶導出ora10gsecDB /expdp$ rm -f sec.dmp sec.logora10gsecDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.logExport: Release .0 - 64bit Production on Monday, 08 March, 201

5、0 9:59:25Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting "SEC"."SYS_EXPORT_SCHEMA_01":

6、 sec/* directory=dump_dir dumpfile=sec.dmp logfile=sec.logEstimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing obj

7、ect type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/

8、CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing 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.

9、914 KB 1 rowsMaster table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded*Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is: /expdp/sec.dmpJob "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:59:326.排除T1表進行備份ora10gsecDB /expdp$ rm -f sec.dmp sec

10、.logora10gsecDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1')"Export: Release .0 - 64bit Production on Monday, 08 March, 2010 10:02:03Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g

11、Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting "SEC"."SYS_EXPORT_SCHEMA_01": sec/* directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1')&q

12、uot;Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object typ

13、e SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/I

14、NDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported "SEC"."T2" 4.914 KB 1 rows. . exported "SEC"."T3" 4.914 KB 1 rowsMaster table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded*Dump file

15、 set for SEC.SYS_EXPORT_SCHEMA_01 is: /expdp/sec.dmpJob "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:02:10排除表T1后T2和T3表被成功導出。7.排除多張表進行備份以排除表T1和T2兩張表為例進行演示ora10gsecDB /expdp$ rm -f sec.dmp sec.logora10gsecDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp log

16、file=sec.log EXCLUDE=TABLE:"IN('T1','T2')"Export: Release .0 - 64bit Production on Monday, 08 March, 2010 10:03:17Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the

17、 Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting "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.Proces

18、sing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type S

19、CHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object ty

20、pe SCHEMA_EXPORT/TABLE/COMMENT. . exported "SEC"."T3" 4.914 KB 1 rowsMaster table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded*Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is: /expdp/sec.dmpJob "SEC"."SYS_EXPORT_SCHEMA_01" succes

21、sfully completed at 10:03:24排除表T1和T2后T3表被成功導出。注意在Linux的bash下特殊字符的轉(zhuǎn)義處理8.使用PARFILE參數(shù)規(guī)避不同操作系統(tǒng)中特殊字符的轉(zhuǎn)義為了規(guī)避不同操作系統(tǒng)上特殊字符轉(zhuǎn)義帶來的麻煩，我們可以使用PARFILE參數(shù)規(guī)避一下這個難題。ora10gsecDB /expdp$ vi sec.paruserid=sec/secdirectory=dump_dirdumpfile=sec.dmplogfile=sec.logEXCLUDE=TABLE:"IN('T1','T2')"ora10gs

22、ecDB /expdp$ rm -f sec.dmp sec.logora10gsecDB /expdp$ expdp parfile=sec.parExport: Release .0 - 64bit Production on Monday, 08 March, 2010 10:10:28Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionW

23、ith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting "SEC"."SYS_EXPORT_SCHEMA_01": parfile=sec.parEstimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBP

24、rocessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProce

25、ssing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported "SEC"."T3" 4.914 KB 1 rowsMa

26、ster table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded*Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is: /expdp/sec.dmpJob "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:10:35在完成特殊條件導出時，推薦將需要的所有參數(shù)統(tǒng)一寫到參數(shù)文件中。9.小結(jié)EXPDP工具與EXP相比不僅僅是效率上的提升，更重要的

27、是功能上的增強。本文中以EXPDP的EXCLUDE選項為例展示了此工具的便捷之處，善用之。Good luck.secooler10.03.08- The End -出至： 關于oracle10g的導出導入工具expdp、impdp=作者: lihui29()發(fā)表于: 2008.12.18 15:54分類:出處: -今天學習了關于Oracle10g的導入導出工具expdp、impdp，總結(jié)一下，以方便查詢。使用expdp1。data pump包括下面幾個部件：The command-line clients, expdp and impdpThe DBMS_DATAPUMP PL/SQL pac

28、kage (also known as the Data Pump API)The DBMS_METADATA PL/SQL package (also known as the Metadata API)2。expdp, impdp和原先的exp,imp不兼容，也就是用exp導出的文件用impdp是無法導入的。3。data pump不支持XML schemas4。使用前必須要先創(chuàng)建目錄：SQL> CREATE OR REPLACE DIRECTORY dpump_dir as '/oracle/oradata/ORCL10/pumpdata'查詢目錄：dba_direc

29、toriesOracle數(shù)據(jù)泵為了提高性能，采用直接路徑的方式，通過數(shù)據(jù)庫建立的DIRECTORY將數(shù)據(jù)導入。這造成了數(shù)據(jù)泵和IMP一個主要的區(qū)別。如果在客戶端進行IMP導入，dmp文件是放在客戶端的。但是如果通過數(shù)據(jù)泵的方式導入，數(shù)據(jù)泵文件總是放在數(shù)據(jù)庫服務器端。5。給導入導出的用戶賦予目錄的讀寫權(quán)限：SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO scott;6。datapump是服務器端的JOB，所以可以在執(zhí)行datapump以后，通過EXIT_CLIENT退出客戶端。通過DBA_DATAPUMP_JOBS視圖可以檢查datapum

30、p作業(yè)的情況，也可以利用ATTACH重新連接上還在進行的JOB。每個datapump可以通過JOB_NAME參數(shù)指定名稱，如果不指定，那么會有默認的名稱，比如上貼中的例子，名稱就是SYS_EXPORT_TABLE_01，通過V$SESSION_LONGOPS也可以查看長時間運行的datapump job的具體內(nèi)容。7.以下參數(shù)影響data pump的性能disk_asynch_io = truedb_block_checking = falsedb_block_checksum = false8.以下參數(shù)設置越高，來允許最大的并行度processessessionsparallel_max_s

31、ervers9. 以下參數(shù)應該被設置大點shared_pool_sizeundo_tablespace實驗以及例子：=創(chuàng)建目錄，dumpdir是自己命名的名稱SQL> create directory dumpdir as 'e:datadump'刪除目錄SQL> drop directory dumpdir ;SQL> grant read,write on directory dumpdir to scott;/導出一個schema:scottC:>expdp scott/tiger dumpfile=scott.dmp directory=dump

32、dir schemas=scottSQL> create user lihui identified by lihui;SQL> grant create session,resource to lihui;SQL> grant read,write on directory dumpdir to lihui;SQL> grant create database link, create synonym, create view to lihui;SQL> grant imp_full_database to scott;/導入到lihui這個schema下C:&

33、gt;impdp scott/tiger directory=dumpdir dumpfile=scott.dmp logfile=scott.log remap_schema=scott:lihui-參數(shù)remap_schema將scott下的內(nèi)容導入導李輝這個schema下提高impdp導入的的速度，與并行操作有關:SQL> show parameter cpuNAME TYPE VALUE- - -cpu_count integer 2parallel_threads_per_cpu integer 2通過parallel參數(shù)導出使用一個以上的線程來顯著的加速作業(yè).每個線程創(chuàng)建一個

34、單獨的轉(zhuǎn)儲文件,因此dumpfile應當擁有和并行度一樣多的項目.可以使用通配符命名文件名,而不用顯式的輸入各個文件名,e.gexpdp lihui/lihui tables=(emp,dept) directory=dumpdir dumpfile=test_%U.dmp parallel=2 job_name=test-%U是通配符,參數(shù)parallel并行度數(shù)據(jù)庫監(jiān)控:監(jiān)控作業(yè)的主要視圖:dba_datapump_sessions,在執(zhí)行任務的時候監(jiān)控前臺進程的會話.dba_datapump_jobs,監(jiān)控在作業(yè)上有多少個工作進程(degree列)在工作.也可以通過alert日志文件查看到進程的信息.處理特