使用expdp匯出分割槽表中的部分分割槽資料
現在需要匯出系統中一個分割槽表中的部分分割槽資料(該分割槽表是按照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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 匯入匯出 Oracle 分割槽表資料Oracle
- 分割槽表匯入資料庫資料庫
- 分割槽表入無分割槽的資料庫資料庫
- 【實驗】【PARTITION】exp匯出分割槽表資料
- 對分割槽表的部分分割槽執行TSPITR
- 使用split對分割槽表再分割槽
- Oracle 12.1.0.2 expdp匯出分割槽表資料遇到BUG慢的原因和解決方法Oracle
- 分割槽表、分割槽索引和全域性索引部分總結索引
- 如何查詢分割槽表的分割槽及子分割槽
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- 自動備份、截斷分割槽表分割槽資料
- oracle分割槽表和分割槽表exchangeOracle
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 【學習筆記】分割槽表和分割槽索引——概念部分（一）筆記索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- Hash分割槽表分割槽數與資料分佈的測試
- 資料庫分割槽表 什麼情況下需要分割槽資料庫
- oracle分割槽表和非分割槽表exchangeOracle
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- Oracle分割槽表的使用Oracle
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- 海量資料處理_表分割槽(分割槽自動維護與歷史分割槽歸檔)
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 分割槽表及分割槽索引建立示例索引
- MySql資料分割槽操作之新增分割槽操作MySql
- 全面學習分割槽表及分割槽索引(8)--增加和收縮表分割槽索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- Oracle中重建表分割槽Oracle
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL