資料泵避免個別表資料的匯出（二）
對於資料泵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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-620716/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵匯出匯入
- 細緻入微：如何使用資料泵匯出表的部分列資料
- Oracle資料泵的匯入和匯出Oracle
- 【資料泵】EXPDP匯出表結構(真實案例)
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle expdp資料泵遠端匯出Oracle
- 資料泵匯出匯入物化檢視（ORA-39083）
- 小景的Dba之路--如何匯出0記錄表以及資料泵的使用
- ClickHouse 資料表匯出和匯入（qbit）
- 資料泵datapump（expdp/impdp）的parfile用法，避免跳脫字元字元
- 運維中資料泵匯出常用的細節小功能兩例運維
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- 避免 OOM，高效匯出百萬級資料的 SpringBoot 實現方案OOMSpring Boot
- 達夢資料庫遷移資料/複製表/匯入匯出2資料庫
- [重慶思莊每日技術分享]-資料泵匯出資料包39006是什麼原因
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- MySQL資料的匯出MySql
- Oracle用資料泵匯入資料包12899的錯誤碼解決方法Oracle
- 分享運維中資料泵匯出常用的細節小功能兩例運維
- oracle12c還原資料庫遇到的問題-將一個11.2.0.1的資料泵匯出檔案匯入12.1.0.2版本報錯Oracle資料庫
- python驅動SAP完成資料匯出（二）Python
- mysql匯出資料MySql
- phpMyAdmin匯入/匯出資料PHP
- Oracle資料匯入匯出Oracle
- sqoop資料匯入匯出OOP
- Oracle 資料匯入匯出Oracle
- 資料泵匯出時報ORA-31623、ORA-06512問題的解決
- 百萬級別資料Excel匯出優化Excel優化
- EasyPoi, Excel資料的匯入匯出Excel
- Mongodb資料的匯出與匯入MongoDB
- 匯入和匯出AWR的資料
- OracleDatabase——資料庫表空間dmp匯出與匯入OracleDatabase資料庫
- 資料庫升級之-資料泵資料庫
- Excel 表匯入資料Excel
- 殺停資料泵
- plsql developmer 匯出資料和生成資料SQLdev
- 資料遷移（1）——通過資料泵表結構批量遷移
- mysql mysqldump只匯出表結構或只匯出資料的實現方法MySql