一、分区级别的导入导出可以导出一个或多个分区，也可以导出所有分区(即整个表)。可以导入所有分区(即整个表)，一个或多个分区以及子分区。对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append | replace参数。二、创建演示环境 1.查看当前数据库的版本SQL>select * from v$version whererownum < 2;BANNER--------------------------------------------------------------------------------Oracle Database11g Enterprise Edition Release 11.2.0.1.0 -Production2.创建一个分区表SQL>alter session setnls_date_format='yyyy-mm-dd';SQL>CREATE TABLEtb_pt (sal_date DATE NOT NULL,sal_id NUMBER NOT NULL,sal_row NUMBER(12) NOT NULL)partition by range(sal_date)(partition sal_11 values less than(to_date('2012-01-01','YYYY-MM-DD')) ,partition sal_12 values less than(to_date('2013-01-01','YYYY-MM-DD')) ,partition sal_13 values less than(to_date('2014-01-01','YYYY-MM-DD')) ,partition sal_14 values less than(to_date('2015-01-01','YYYY-MM-DD')) ,partition sal_15 values less than(to_date('2016-01-01','YYYY-MM-DD')) ,partition sal_16 values less than(to_date('2017-01-01','YYYY-MM-DD')) ,partition sal_other values lessthan (maxvalue)) nologging;3.创建一个唯一索引CREATE UNIQUE INDEX tb_pt_ind1 ON tb_pt(sal_date) nologging;4.为分区表生成数据SQL>INSERT INTOtb_ptSELECT TRUNC(SYSDATE)+ROWNUM, dbms_random.random, ROWNUMFROM dualCONNECT BYLEVEL<=5000;SQL>commit;SQL>select count(1) from tb_pt partition(sal_11);COUNT(1)----------300SQL>select count(1) from tb_pt partition(sal_other);COUNT(1)----------2873SQL>select * from tb_pt partition(sal_12) where rownum < 3;SAL_DATE SAL_ID SAL_ROW--------- --------------------01-JAN-12 -1.356E+09 30102-JAN-12 -761530183 302三、使用exp/imp导出导入分区表数据1.导出整个分区表[oracle@node1 ~]$ expscott/tiger file='/u02/dmp/tb_pt.dmp' log='/u02/dmp/tb_pt.log' tables=tb_ptExport: Release 11.2.0.1.0 - Production on Wed Mar 913:52:18 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport done in US7ASCII character set and AL16UTF16 NCHARcharacter setserver uses ZHS16GBK character set (possible charsetconversion)About to export specified tables via Conventional Path.... . exporting table TB_PT. . exporting partition SAL_11 300 rows exported. . exporting partition SAL_12 366 rows exported. . exporting partition SAL_13 365 rows exported. . exporting partition SAL_14 365 rows exported. . exporting partition SAL_15 365 rows exported. . exporting partition SAL_16 366 rows exported. . exporting partition SAL_OTHER 2873 rows exportedEXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.Export terminated successfully with warnings.[oracle@node1 ~]$ oerr exp 0009100091, 00000, "Exporting questionablestatistics."// *Cause: Export was able export statistics, but thestatistics may not be// usuable. The statistics are questionable because oneor more of// the following happened during export: a row erroroccurred, client// character set or NCHARSET does not match with theserver, a query// clause was specified on export, only certainpartitions or// subpartitions were exported, or a fatal error occurredwhile// processing a table.// *Action: To export non-questionable statistics, changethe client character// set or NCHARSET to match the server, export with noquery clause,// export complete tables. If desired, import parameterscan be// supplied so that only non-questionable statistics willbe imported,// and all questionable statistics will be recalculated.在上面的导出中出现了错误提示，即EXP-00091，该错误表明exp工具所在的环境变量中的NLS_LANG与DB中的NLS_CHARACTERSET不一致尽管该错误对最终的数据并无影响，但调整该参数来避免异常还是有必要的。因此需要将其设置为一致即可解决上述的错误提示。SQL>select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16GBK[oracle@node1 ~]$ export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'经过上述设置之后再次导出正常，过程略。2.导出单个分区[oracle@node1 ~]$ exp scott/tigerfile='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/dmp/tb_pt_sal_16.log'tables=tb_pt:sal_16Export: Release 11.2.0.1.0 - Production on Wed Mar 913:52:38 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport done in ZHS16GBK character set and AL16UTF16 NCHARcharacter setAbout to export specified tables via Conventional Path.... . exporting table TB_PT. . exporting partition SAL_16 366 rows exportedEXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.Export terminated successfully with warnings在上面的导出过程中再次出现了统计信息错误的情况，因此采取了对该对象收集统计信息，但并不能解决该错误，但在exp命令行中增加statistics=none即可，如下：[oracle@node1 ~]$ expscott/tiger file='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/dmp/tb_pt_sal_16.log' /> tables=tb_pt:sal_16 statistics=none如果要导出多个分区，则在tables参数中增加分区数。如：tables=(tb_pt:sal_15,tb_pt:sal_16)3.使用imp工具生成创建分区表的DDL语句[oracle@node1 ~]$ impscott/tiger tables=tb_pt indexfile='/u02/dmp/cr_tb_pt.sql' /> file='/u02/dmp/tb_pt.dmp' ignore=yExport: Release 11.2.0.1.0 - Production on Wed Mar 913:54:38 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport file created by EXPORT:V11.02.00 via conventionalpathimport done in US7ASCII character set and AL16UTF16 NCHARcharacter setimport server uses ZHS16GBK character set (possiblecharset conversion). . skipping partition"TB_PT":"SAL_11" . . skipping partition"TB_PT":"SAL_12" . . skipping partition"TB_PT":"SAL_13" . . skipping partition"TB_PT":"SAL_14" . . skipping partition"TB_PT":"SAL_15" . . skipping partition"TB_PT":"SAL_16" . . skipping partition"TB_PT":"SAL_OTHER" Import terminated successfully without warnings.4.导入单个分区(使用先前备份的单个分区导入文件)SQL>alter tabletb_pt truncate partitionsal_16; --导入前先将分区实现truncateTable truncated.SQL>select count(1) from tb_pt partition(sal_16);COUNT(1)----------0SQL> ho imp scott/tigertables=tb_pt:sal_16 file='/u02/dmp/tb_pt_sal_16.dmp' ignore=yExport: Release 11.2.0.1.0 - Production on Wed Mar 913:55:39 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport file created by EXPORT:V11.02.00 via conventionalpathimport done in US7ASCII character set and AL16UTF16 NCHARcharacter setimport server uses ZHS16GBK character set (possiblecharset conversion). importing SCOTT's objects into SCOTT. importing SCOTT's objects into SCOTT. . importing partition "TB_PT":"SAL_16"IMP-00058: ORACLE error 1502 encounteredORA-01502: index 'SCOTT.TB_PT_IND1' or partition of suchindex is in unusable stateImport terminated successfully with warnings.收到了ORA-01502错误，下面查看索引的状态，并对其重建索引后再执行导入SQL>select index_name ,statusfrom dba_indexes wheretable_name='TB_PT'; --查看索引的状态INDEX_NAME STATUS--------------------------------------TB_PT_IND1 UNUSABLESQL>alter indexTB_PT_IND1 rebuild online; --重建索引Index altered.SQL> ho imp scott/tigertables=tb_pt:sal_16 file='/u02/dmp/tb_pt_sal_16.dmp' ignore=y --再次导入成功Export: Release 11.2.0.1.0 - Production on Wed Mar 913:56:15 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport file created by EXPORT:V11.02.00 via conventionalpathimport done in US7ASCII character set and AL16UTF16 NCHARcharacter setimport server uses ZHS16GBK character set (possiblecharset conversion). importing SCOTT's objects into SCOTT. importing SCOTT's objects into SCOTT. . importing partition"TB_PT":"SAL_16" 366 rows importedImport terminated successfully without warnings.SQL>select count(*) from tb_pt partition(sal_16);COUNT(*)----------3665.导入整个表SQL>truncate tabletb_pt; --首先truncate 整个表Table truncated.SQL> ho imp scott/tigertables=tb_pt file='/u02/dmp/tb_pt.dmp' ignore=y indexes=yExport: Release 11.2.0.1.0 - Production on Wed Mar 913:57:10 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport file created by EXPORT:V11.02.00 via conventionalpathimport done in US7ASCII character set and AL16UTF16 NCHARcharacter setimport server uses ZHS16GBK character set (possiblecharset conversion). importing SCOTT's objects into SCOTT. importing SCOTT's objects into SCOTT. . importing partition"TB_PT":"SAL_11" 298 rows imported. . importing partition"TB_PT":"SAL_12" 366 rows imported. . importing partition"TB_PT":"SAL_13" 365 rows imported. . importing partition"TB_PT":"SAL_14" 365 rows imported. . importing partition"TB_PT":"SAL_15" 365 rows imported. . importing partition"TB_PT":"SAL_16" 366 rows imported. . importing partition"TB_PT":"SAL_OTHER" 2875 rows importedImport terminated successfully without warnings.SQL>select count(1) from tb_pt partition(sal_other);COUNT(1)----------2875四、使用expdp/impdb来实现分区表的导入导出1.查看导入导出的目录设置SQL>select directory_name,directory_pathfrom dba_directories wheredirectory_name='DMP';DIRECTORY_NAME DIRECTORY_PATH------------------------------------------------------------------------------------------DMP /u02/dmp2.为分区表创建一个本地索引create index tb_pt_local_idxon tb_pt(sal_id)local(partition local1,partition local2,partition local3,partition local4,partition local5,partition local6,partition local7);3.导出整个表[oracle@node1 ~]$ expdpscott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_ptparallel=3Export: Release 11.2.0.1.0 - Production on Wed Mar 914:04:28 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsStarting"SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dmpdumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3 Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 512 KB. . exported"SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows. . exported"SCOTT"."TB_PT":"SAL_11" 12.54 KB 298 rows. . exported"SCOTT"."TB_PT":"SAL_12" 14.22 KB 366 rows. . exported"SCOTT"."TB_PT":"SAL_13" 14.18 KB 365 rows. . exported"SCOTT"."TB_PT":"SAL_14" 14.18 KB 365 rows. . exported"SCOTT"."TB_PT":"SAL_15" 14.19 KB 365 rows. . exported"SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rowsProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object typeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object typeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSMaster table"SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:/u02/dmp/tb_pt.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01"successfully completed at 14:04:514.导出多个分区[oracle@node1 ~]$ expdpscott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log /> tables=(tb_pt:sal_16,tb_pt:sal_other)parallel=2Export: Release 11.2.0.1.0 - Production on Wed Mar 914:08:06 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsStarting"SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dmpdumpfile=tb_pts.dmp logfile=tb_pt.log tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2 --*/Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KB. . exported"SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows. . exported"SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rowsProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object typeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object typeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSMaster table"SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:/u02/dmp/tb_pts.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01"successfully completed at 14:08:175.截断分区sal_otherSQL>alter tabletb_pt truncate partition(sal_other);Table truncated.SQL>select count(*) from tb_pt partition(sal_other);COUNT(*)----------0SQL>select index_name,status,partitioned fromdba_indexes where table_name='TB_PT'; --查看索引的状态, TB_PT_IND1不可用INDEX_NAME STATUS PAR-------------------------------------- ---TB_PT_IND1 UNUSABLE NOTB_PT_LOCAL_IDX N/A YESSQL>select index_name ,partition_name, status fromdba_ind_partitions where index_owner='SCOTT';INDEX_NAME PARTITION_NAME STATUS------------------------------------------------------------ --------TB_PT_LOCAL_IDX LOCAL1 USABLETB_PT_LOCAL_IDX LOCAL2 USABLETB_PT_LOCAL_IDX LOCAL3 USABLETB_PT_LOCAL_IDX LOCAL4 USABLETB_PT_LOCAL_IDX LOCAL5 USABLETB_PT_LOCAL_IDX LOCAL6 USABLETB_PT_LOCAL_IDX LOCAL7 USABLE6.导入单个分区[oracle@node1 ~]$ impdpscott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log /> tables=tb_pt:sal_other skip_unusable_indexes=ytable_exists_action=replaceImport: Release 11.2.0.1.0 - Production on Wed Mar 914:13:28 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table"SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting"SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmpdumpfile=tb_pts.dmp logfile=tb_pt_imp.log tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace--*/Processing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported"SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rowsProcessing object typeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object typeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SCOTT"."SYS_IMPORT_TABLE_01"successfully completed at 14:13:33SQL>select index_name,status,partitioned fromdba_indexes where table_name='TB_PT';INDEX_NAME STATUS PAR-------------------------------------- ---TB_PT_IND1 VALID NOTB_PT_LOCAL_IDX N/A YES从上面的导入情况可以看出，尽管执行了truncate partition，然而使用impdp导入工具，并且使用参数table_exists_action=replace可以避免使用imp导入时唯一和主键索引需要重建的问题。注意，如果没有使用table_exists_action=replace参数，将会收到ORA-39151错误，如下ORA-39151: Table "SCOTT"."TB_PT"exists. All dependent metadata and data will be skipped due to table_exists_action of skip7.导入整个表[oracle@node1 ~]$ impdpscott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log /> tables=tb_pt skip_unusable_indexes=ytable_exists_action=replaceImport: Release 11.2.0.1.0 - Production on Wed Mar 914:17:35 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.Connected to: Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table"SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting"SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmpdumpfile=tb_pt.dmp logfile=tb_pt_fullimp.logtables=tb_pt skip_unusable_indexes=ytable_exists_action=replace --*/Processing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported"SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows. . imported"SCOTT"."TB_PT":"SAL_11" 12.54 KB 298 rows. . imported"SCOTT"."TB_PT":"SAL_12" 14.22 KB 366 rows. . imported"SCOTT"."TB_PT":"SAL_13" 14.18 KB 365 rows. . imported"SCOTT"."TB_PT":"SAL_14" 14.18 KB 365 rows. . imported"SCOTT"."TB_PT":"SAL_15" 14.19 KB 365 rows. . imported "SCOTT"."TB_PT":"SAL_16"14.23 KB 366 rowsProcessing object typeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object typeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SCOTT"."SYS_IMPORT_TABLE_01"successfully completed at 14:17:40五、参数skip_unusable_indexes的作用SQL>show parameter skipNAME TYPE VALUE----------------------------------------------- ------------------------------skip_unusable_indexes booleanTRUE该参数允许在导入分区数据时延迟对索引的处理，即先将数据导入，导入后再来重建索引分区。在命令行导入中未指定导入参数skip_unusable_indexes时，则对于索引相关的问题，根据数据库初始化参数的值来确定。在命令行导入中如果指定了参数skip_unusable_indexes时，则该参数的值优先于数据库初始化参数的设定值。skip_unusable_indexes=y对unique index不起作用，因为此时的unique index扮演者constraint的作用，所以在insert数据时index必须被更新。对于单个分区导入时PK，unique index的处理，必须先重建索引然后进行导入。使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题，即ORA-01502错误。