WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606
全部博文(599)
分类: 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