Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2881091
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2009-12-07 16:10:31

对于数据泵EXPDP/IMPDP而言,功能比普通EXP/IMP功能要强的多,因此也可以实现一些普通导出导入工具很难完成的工作。

这一篇介绍如何对分区表实现这个功能。

数据泵避免个别表数据的导出:http://yangtingkun.itpub.net/post/468/490624


上一篇文章提到了,如何利用EXCLUDE的方式指定TABLE_DATA,来避免导出个别表的数据。

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt.dp tables=t,tt exclude=table_data:"='T'"

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 24 11, 2009 20:06:19

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, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt.dp tables=t,tt exclude=table_data:"='T'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TT" 28.88 KB 51 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 20:06:47

但是这个方法对于分区表似乎无效,下面将T表变为分区表:

SQL> drop table t purge;

Table dropped.

SQL> create table t
2 (id number,
3 name varchar2(30))
4 partition by range (id)
5 (partition p1 values less than (10000),
6 partition p2 values less than (20000),
7 partition p3 values less than (maxvalue));

Table created.

SQL> insert into t
2 select rownum, object_name
3 from all_objects;

75092 rows created.

SQL> commit;

Commit complete.

下面再次执行同样的EXPDP语句:

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt1.dp tables=t,tt exclude=table_data:"='T'"

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 24 11, 2009 20:12:15

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, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt1.dp tables=t,tt exclude=table_data:"='T'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T":"P3" 1.649 MB 55093 rows
. . exported "TEST"."T":"P2" 355.9 KB 10000 rows
. . exported "TEST"."T":"P1" 260.9 KB 9999 rows
. . exported "TEST"."TT" 28.88 KB 51 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt1.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 20:12:20

显然EXCLUDE方式针对TABLE_DATA并没有生效,下面尝试增加分区信息:

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt2.dp tables=t,tt exclude=table_data:"='T:P1'"

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 24 11, 2009 20:14:01

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, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt2.dp tables=t,tt exclude=table_data:"='T:P1'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T":"P3" 1.649 MB 55093 rows
. . exported "TEST"."T":"P2" 355.9 KB 10000 rows
. . exported "TEST"."T":"P1" 260.9 KB 9999 rows
. . exported "TEST"."TT" 28.88 KB 51 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt2.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 20:14:05

经过多次的测试最后发现,这里不需要指定表名T,而需要指定分区的名称:

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt.dp tables=t,tt exclude=table_data:"in ('P1','P2','P3')"

Export: Release 10.2.0.3.0 - 64bit Production on
星期二, 24 11, 2009 20:16: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, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt.dp tables=t,tt exclude=table_data:"in ('P1','P2','P3')"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TT" 28.88 KB 51 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 20:16:32

显然对于分区表而言,TABLE_DATA中指定的不再是表名而是分区名或子分区名。

对于当前的情况,还可以用下面的导出方式进行简化:

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt3.dp tables=t,tt exclude=table_data:"like 'P%'"

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 24 11, 2009 20:19:06

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, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt3.dp tables=t,tt exclude=table_data:"like 'P%'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TT" 28.88 KB 51 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt3.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 20:19:10

或者直接使用不等的方式来进行排除:

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt.dp tables=t,tt exclude=table_data:"!= 'TT'"

Export: Release 10.2.0.3.0 - 64bit Production on 星期三, 25 11, 2009 16:03:47

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, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_tt.dp tables=t,tt exclude=table_data:"!= 'TT'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."TT" 28.88 KB 51 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_tt.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:03:51

阅读(899) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~