数据泵避免个别表数据的导出（二）

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

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

数据泵避免个别表数据的导出：http://blog.csdn.net/bbqk9/archive/2011/05/03/6386225.aspx

 

上一篇文章提到了，如何利用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