ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用expdp导出分区表中的部分分区数据

使用expdp导出分区表中的部分分区数据

原创 Linux操作系统 作者:myownstars 时间:2011-09-13 18:16:24 0 删除 编辑

现在需要导出系统中一个分区表中的部分分区数据(该分区表是按照date列按日分区),起初是按照分区键的时间范围进行查询,采取expdp的query条件;
但是查看实际的输出信息发现,expdp是把该分区表的所有分区都导出了
[oracle@justinpump]$  expdp justin/justin dumpfile=justin_2011_04_a.dmp directory=pump logfile=2011_04_a.log tables=justin query=justin:\" where create_time between to_date\(\'2011-03-31\',\'yyyy-mm-dd\'\) and to_date\(\'2011-04-15\',\'yyyy-mm-dd\'\)\"

Export: Release 11.2.0.2.0 - Production on Tue Sep 13 16:24:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS_EXPORT_TABLE_05":  justin/******** dumpfile=justin_2011_04_a.dmp directory=pump logfile=2011_04_a.log tables=justin query=justin:" where create_time between to_date('2011-03-31','yyyy-mm-dd') and to_date('2011-04-15','yyyy-mm-dd')"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 301.0 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "justin":"P110714"          21.97 KB       0 rows
. . exported "justin":"P110712"          21.97 KB       0 rows
. . exported "justin":"P110728"          21.97 KB       0 rows
. . exported "justin":"P110721"          21.97 KB       0 rows
. . exported "justin":"P110726"          21.97 KB       0 rows
. . exported "justin":"P110720"          21.97 KB       0 rows
. . exported "justin":"P110707"          21.97 KB       0 rows
. . exported "justin":"P110801"          21.97 KB       0 rows
. . exported "justin":"P110705"          21.97 KB       0 rows
. . exported "justin":"P110727"          21.97 KB       0 rows
. . exported "justin":"P110713"          21.97 KB       0 rows
. . exported "justin":"P110722"          21.97 KB       0 rows
. . exported "justin":"P110729"          21.97 KB       0 rows
. . exported "justin":"P110719"          21.97 KB       0 rows
. . exported "justin":"P110730"          21.97 KB       0 rows
. . exported "justin":"P110715"          21.97 KB       0 rows
. . exported "justin":"P110803"          21.97 KB       0 rows
. . exported "justin":"P110706"          21.97 KB       0 rows
. . exported "justin":"P110723"          21.97 KB       0 rows
. . exported "justin":"P110802"          21.97 KB       0 rows
. . exported "justin":"P110708"          21.97 KB       0 rows
. . exported "justin":"P110709"          21.97 KB       0 rows
. . exported "justin":"P110804"          21.97 KB       0 rows
. . exported "justin":"P110702"          21.97 KB       0 rows
. . exported "justin":"P110716"          21.97 KB       0 rows
. . exported "justin":"P110731"          21.97 KB       0 rows
. . exported "justin":"P110724"          21.97 KB       0 rows
. . exported "justin":"P110805"          21.97 KB       0 rows
. . exported "justin":"P110725"          21.97 KB       0 rows
. . exported "justin":"P110710"          21.97 KB       0 rows
. . exported "justin":"P110703"          21.97 KB       0 rows
. . exported "justin":"P110806"          21.97 KB       0 rows
. . exported "justin":"P110718"          21.97 KB       0 rows
. . exported "justin":"P110717"          21.97 KB       0 rows
. . exported "justin":"P110513"          21.97 KB       0 rows
. . exported "justin":"P110704"          21.97 KB       0 rows
. . exported "justin":"P110524"          21.97 KB       0 rows
. . exported "justin":"P110711"          21.97 KB       0 rows
. . exported "justin":"P110525"          21.97 KB       0 rows
. . exported "justin":"P110531"          21.97 KB       0 rows
. . exported "justin":"P110527"          21.97 KB       0 rows
. . exported "justin":"P110528"          21.97 KB       0 rows
. . exported "justin":"P110526"          21.97 KB       0 rows
. . exported "justin":"P110807"          21.97 KB       0 rows
. . exported "justin":"P110808"          21.97 KB       0 rows
. . exported "justin":"P110630"          21.97 KB       0 rows
. . exported "justin":"P110625"          21.97 KB       0 rows
. . exported "justin":"P110621"          21.97 KB       0 rows
. . exported "justin":"P110624"          21.97 KB       0 rows
. . exported "justin":"P110601"          21.97 KB       0 rows
. . exported "justin":"P110629"          21.97 KB       0 rows
. . exported "justin":"P110401"          1.853 GB 4045763 rows

只好采用显示标志分区的办法,即将需要导出的表分区挨个写出来
[oracle@justinpump]$ expdp justin/justin dumpfile=justin_2011_04_a.dmp directory=pump logfile=2011_04_a.log tables=justin:P110401,justin:P110402,justin:P110403,justin:P110404,justin:P110405,justin:P110406,justin:P110407,justin:P110408,justin:P110409,justin:P110410,justin:P110411,justin:P110412,justin:P110413,justin:P110414,justin:P110415

Export: Release 11.2.0.2.0 - Production on Tue Sep 13 16:34:22 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS_EXPORT_TABLE_06":  justin/******** dumpfile=justin_2011_04_a.dmp directory=pump logfile=2011_04_a.log tables=justin:P110401,justin:P110402,justin:P110403,justin:P110404,justin:P110405,justin:P110406,justin:P110407,justin:P110408,justin:P110409,justin:P110410,justin:P110411,justin:P110412,justin:P110413,justin:P110414,justin:P110415
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.67 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "justin":"P110401"          1.854 GB 4045763 rows
. . exported "justin":"P110402"          1.767 GB 3876183 rows
. . exported "justin":"P110408"          1.614 GB 3438278 rows
. . exported "justin":"P110403"          1.535 GB 3394902 rows
. . exported "justin":"P110414"          1.293 GB 2664206 rows
. . exported "justin":"P110407"          1.278 GB 2669200 rows
. . exported "justin":"P110415"          1.292 GB 2675723 rows
. . exported "justin":"P110413"          1.206 GB 2508370 rows
. . exported "justin":"P110409"          1.206 GB 2571078 rows
. . exported "justin":"P110412"          1.077 GB 2233418 rows
. . exported "justin":"P110404"          1007. MB 2035594 rows
. . exported "justin":"P110406"          931.5 MB 1874878 rows
. . exported "justin":"P110410"          926.9 MB 1863841 rows
. . exported "justin":"P110411"          913.7 MB 1833223 rows
. . exported "justin":"P110405"          905.4 MB 1832095 rows
Master table "SYS_EXPORT_TABLE_06" successfully loaded/unloaded
******************************************************************************
Dump file set for justin.SYS_EXPORT_TABLE_06 is:
  /data/pump/justin_2011_04_a.dmp
Job "SYS_EXPORT_TABLE_06" successfully completed at 16:47:26
此时方才导出成功

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-707365/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3111822