oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据

1、【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据库对象上一篇 / 下一篇 2010-03-08 22:54:51 / 个人分类：备份与恢复查看( 952 )/ 评论( 2 ) / 评分( 5 / 0 )使用EXPDP逻辑备份工具的EXCLUDE选项可以指定那类数据库对象不被导出，EXPDP工具的前身EXP如果想要完成同样的任务非常的困难。我们以排除部分表为例看一下这个选项带给我们的便利。如果在命令行中完成备份，特殊字符的转义需要特别注意（我这里使用的是Linux操作系统）。1.EXPDP帮助中的描述信息ora10gsecDB /expdp$ expdp -helpEXC

2、LUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.2.创建directory数据库对象，并将读写权限授予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.确认操作系统信息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用户下创建几张表用于后续的测试创建三张表T1、T2和T3，每张表中初始化一条数据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下特殊字符的转义处理8.使用PARFILE参数规避不同操作系统中特殊字符的转义为了规避不同操作系统上特殊字符转义带来的麻烦，我们可以使用PARFILE参数规避一下这个难题。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在完成特殊条件导出时，推荐将需要的所有参数统一写到参数文件中。9.小结EXPDP工具与EXP相比不仅仅是效率上的提升，更重要的

27、是功能上的增强。本文中以EXPDP的EXCLUDE选项为例展示了此工具的便捷之处，善用之。Good luck.secooler10.03.08- The End -出至： 关于oracle10g的导出导入工具expdp、impdp=作者: lihui29()发表于: 2008.12.18 15:54分类:出处: -今天学习了关于Oracle10g的导入导出工具expdp、impdp，总结一下，以方便查询。使用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。使用前必须要先创建目录：SQL> CREATE OR REPLACE DIRECTORY dpump_dir as '/oracle/oradata/ORCL10/pumpdata'查询目录：dba_direc

29、toriesOracle数据泵为了提高性能，采用直接路径的方式，通过数据库建立的DIRECTORY将数据导入。这造成了数据泵和IMP一个主要的区别。如果在客户端进行IMP导入，dmp文件是放在客户端的。但是如果通过数据泵的方式导入，数据泵文件总是放在数据库服务器端。5。给导入导出的用户赋予目录的读写权限：SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO scott;6。datapump是服务器端的JOB，所以可以在执行datapump以后，通过EXIT_CLIENT退出客户端。通过DBA_DATAPUMP_JOBS视图可以检查datapum

30、p作业的情况，也可以利用ATTACH重新连接上还在进行的JOB。每个datapump可以通过JOB_NAME参数指定名称，如果不指定，那么会有默认的名称，比如上贴中的例子，名称就是SYS_EXPORT_TABLE_01，通过V$SESSION_LONGOPS也可以查看长时间运行的datapump job的具体内容。7.以下参数影响data pump的性能disk_asynch_io = truedb_block_checking = falsedb_block_checksum = false8.以下参数设置越高，来允许最大的并行度processessessionsparallel_max_s

31、ervers9. 以下参数应该被设置大点shared_pool_sizeundo_tablespace实验以及例子：=创建目录，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-参数remap_schema将scott下的内容导入导李辉这个schema下提高impdp导入的的速度，与并行操作有关:SQL> show parameter cpuNAME TYPE VALUE- - -cpu_count integer 2parallel_threads_per_cpu integer 2通过parallel参数导出使用一个以上的线程来显著的加速作业.每个线程创建一个

34、单独的转储文件,因此dumpfile应当拥有和并行度一样多的项目.可以使用通配符命名文件名,而不用显式的输入各个文件名,e.gexpdp lihui/lihui tables=(emp,dept) directory=dumpdir dumpfile=test_%U.dmp parallel=2 job_name=test-%U是通配符,参数parallel并行度数据库监控:监控作业的主要视图:dba_datapump_sessions,在执行任务的时候监控前台进程的会话.dba_datapump_jobs,监控在作业上有多少个工作进程(degree列)在工作.也可以通过alert日志文件查看到进程的信息.处理特