现在需要导出系统中一个分区表中的部分分区数据(该分区表是按照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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-707365/