导入导出 Oracle 分区表数据

《导入导出 Oracle 分区表数据》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

导入导出Oracle分区表数据-CUUG　　--****************************　　--导入导出Oracle分区表数据　　--****************************　　导入导入Oracle分区表数据是OracleDBA经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出分区表数据。　　一、分区级别的导入导出　　可以导出一个或多个分区，也可以导出所有分区(即整个表)。　　可以导入所有分区(即整个表)，一个或多个分区以及子分区。　　对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append|replace参数。　　二、创建演示环境　　1.查看当前数据库的版本　　SQL>select*fromv$versionwhererownum<2;　　BANNER　　--------------------------------------------------------------------------------　　OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　2.创建一个分区表　　SQL>altersessionsetnls_date_format='yyyy-mm-dd';　　SQL>CREATETABLEtb_pt(　　sal_dateDATENOTNULL,　　sal_idNUMBERNOTNULL,　　sal_rowNUMBER(12)NOTNULL)17 　　partitionbyrange(sal_date)　　(　　partitionsal_11valueslessthan(to_date('2012-01-01','YYYY-MM-DD')),　　partitionsal_12valueslessthan(to_date('2013-01-01','YYYY-MM-DD')),　　partitionsal_13valueslessthan(to_date('2014-01-01','YYYY-MM-DD')),　　partitionsal_14valueslessthan(to_date('2015-01-01','YYYY-MM-DD')),　　partitionsal_15valueslessthan(to_date('2016-01-01','YYYY-MM-DD')),　　partitionsal_16valueslessthan(to_date('2017-01-01','YYYY-MM-DD')),　　partitionsal_othervalueslessthan(maxvalue)　　)nologging;　　3.创建一个唯一索引　　CREATEUNIQUEINDEXtb_pt_ind1　　ONtb_pt(sal_date)nologging;　　4.为分区表生成数据　　SQL>INSERTINTOtb_pt　　SELECTTRUNC(SYSDATE)+ROWNUM,dbms_random.random,ROWNUM　　FROMdual　　CONNECTBYLEVEL<=5000;<>　　SQL>commit;　　SQL>selectcount(1)fromtb_ptpartition(sal_11);　　COUNT(1)　　----------　　300　　SQL>selectcount(1)fromtb_ptpartition(sal_other);　　COUNT(1)　　----------　　287317 　　SQL>select*fromtb_ptpartition(sal_12)whererownum<3;　　SAL_DATESAL_IDSAL_ROW　　-----------------------------　　01-JAN-12-1.356E+09301　　02-JAN-12-761530183302　　三、使用exp/imp导出导入分区表数据　　1.导出整个分区表　　[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt.dmp'log='/u02/dmp/tb_pt.log'tables=tb_pt　　Export:Release11.2.0.1.0-ProductiononWedMar913:52:182011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingo　　ExportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset　　serverusesZHS16GBKcharacterset(possiblecharsetconversion)　　AbouttoexportspecifiedtablesviaConventionalPath...　　..exportingtableTB_PT　　..exportingpartitionSAL_11300rowsexported　　..exportingpartitionSAL_12366rowsexported　　..exportingpartitionSAL_13365rowsexported　　..exportingpartitionSAL_14365rowsexported　　..exportingpartitionSAL_15365rowsexported　　..exportingpartitionSAL_16366rowsexported　　..exportingpartitionSAL_OTHER2873rowsexported17 　　EXP-00091:Exportingquestionablestatistics.　　EXP-00091:Exportingquestionablestatistics.　　Exportterminatedsuccessfullywithwarnings.　　[oracle@node1~]$oerrexp00091　　00091,00000,"Exportingquestionablestatistics."　　//*Cause:Exportwasableexportstatistics,butthestatisticsmaynotbe　　//usuable.Thestatisticsarequestionablebecauseoneormoreof　　//thefollowinghappenedduringexport:arowerroroccurred,client　　//charactersetorNCHARSETdoesnotmatchwiththeserver,aquery　　//clausewasspecifiedonexport,onlycertainpartitionsor　　//subpartitionswereexported,orafatalerroroccurredwhile　　//processingatable.　　//*Action:Toexportnon-questionablestatistics,changetheclientcharacter　　//setorNCHARSETtomatchtheserver,exportwithnoqueryclause,　　//exportcompletetables.Ifdesired,importparameterscanbe　　//suppliedsothatonlynon-questionablestatisticswillbeimported,　　//andallquestionablestatisticswillberecalculated.　　在上面的导出中出现了错误提示，即EXP-00091，该错误表明exp工具所在的环境变量中的NLS_LANG与DB中的NLS_CHARACTERSET不一致　　尽管该错误对最终的数据并无影响，但调整该参数来避免异常还是有必要的。因此需要将其设置为一致即可解决上述的错误提示。　　SQL>selectuserenv('language')fromdual;　　USERENV('LANGUAGE')　　----------------------------------------------------　　AMERICAN_AMERICA.ZHS16GBK　　[oracle@node1~]$exportNLS_LANG='AMERICAN_AMERICA.ZHS16GBK'　　经过上述设置之后再次导出正常，过程略。17 　　2.导出单个分区　　[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt_sal_16.dmp'log='/u02/dmp/tb_pt_sal_16.log'tables=tb_pt:sal_16　　Export:Release11.2.0.1.0-ProductiononWedMar913:52:382011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingo　　ExportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset　　AbouttoexportspecifiedtablesviaConventionalPath...　　..exportingtableTB_PT　　..exportingpartitionSAL_16366rowsexported　　EXP-00091:Exportingquestionablestatistics.　　EXP-00091:Exportingquestionablestatistics.　　Exportterminatedsuccessfullywithwarnings　　在上面的导出过程中再次出现了统计信息错误的情况，因此采取了对该对象收集统计信息，但并不能解决该错误，但在exp命令行中增　　加statistics=none即可，如下：　　[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt_sal_16.dmp'log='/u02/dmp/tb_pt_sal_16.log'/　　>tables=tb_pt:sal_16statistics=none　　如果要导出多个分区，则在tables参数中增加分区数。如：tables=(tb_pt:sal_15,tb_pt:sal_16)　　3.使用imp工具生成创建分区表的DDL语句　　[oracle@node1~]$impscott/tigertables=tb_ptindexfile='/u02/dmp/cr_tb_pt.sql'/17 　　>file='/u02/dmp/tb_pt.dmp'ignore=y　　Export:Release11.2.0.1.0-ProductiononWedMar913:54:382011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingo　　ExportfilecreatedbyEXPORT:V11.02.00viaconventionalpath　　importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset　　importserverusesZHS16GBKcharacterset(possiblecharsetconversion)　　..skippingpartition"TB_PT":"SAL_11"　　..skippingpartition"TB_PT":"SAL_12"　　..skippingpartition"TB_PT":"SAL_13"　　..skippingpartition"TB_PT":"SAL_14"　　..skippingpartition"TB_PT":"SAL_15"　　..skippingpartition"TB_PT":"SAL_16"　　..skippingpartition"TB_PT":"SAL_OTHER"　　Importterminatedsuccessfullywithoutwarnings.　　4.导入单个分区(使用先前备份的单个分区导入文件)　　SQL>altertabletb_pttruncatepartitionsal_16;--导入前先将分区实现truncate　　Tabletruncated.　　SQL>selectcount(1)fromtb_ptpartition(sal_16);　　COUNT(1)　　----------　　017 　　SQL>hoimpscott/tigertables=tb_pt:sal_16file='/u02/dmp/tb_pt_sal_16.dmp'ignore=y　　Export:Release11.2.0.1.0-ProductiononWedMar913:55:392011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingo　　ExportfilecreatedbyEXPORT:V11.02.00viaconventionalpath　　importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset　　importserverusesZHS16GBKcharacterset(possiblecharsetconversion)　　.importingSCOTT'sobjectsintoSCOTT　　.importingSCOTT'sobjectsintoSCOTT　　..importingpartition"TB_PT":"SAL_16"　　IMP-00058:ORACLEerror1502encountered　　ORA-01502:index'SCOTT.TB_PT_IND1'orpartitionofsuchindexisinunusablestate　　Importterminatedsuccessfullywithwarnings.　　收到了ORA-01502错误，下面查看索引的状态，并对其重建索引后再执行导入　　SQL>selectindex_name,statusfromdba_indexeswheretable_name='TB_PT';--查看索引的状态　　INDEX_NAMESTATUS　　--------------------------------------　　TB_PT_IND1UNUSABLE　　SQL>alterindexTB_PT_IND1rebuildonline;--重建索引　　Indexaltered.17 　　SQL>hoimpscott/tigertables=tb_pt:sal_16file='/u02/dmp/tb_pt_sal_16.dmp'ignore=y--再次导入成功　　Export:Release11.2.0.1.0-ProductiononWedMar913:56:152011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingo　　ExportfilecreatedbyEXPORT:V11.02.00viaconventionalpath　　importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset　　importserverusesZHS16GBKcharacterset(possiblecharsetconversion)　　.importingSCOTT'sobjectsintoSCOTT　　.importingSCOTT'sobjectsintoSCOTT　　..importingpartition"TB_PT":"SAL_16"366rowsimported　　Importterminatedsuccessfullywithoutwarnings.　　SQL>selectcount(*)fromtb_ptpartition(sal_16);　　COUNT(*)　　----------　　366　　5.导入整个表　　SQL>truncatetabletb_pt;--首先truncate整个表　　Tabletruncated.　　SQL>hoimpscott/tigertables=tb_ptfile='/u02/dmp/tb_pt.dmp'ignore=yindexes=y　　Export:Release11.2.0.1.0-ProductiononWedMar913:57:102011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.17 　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingo　　ExportfilecreatedbyEXPORT:V11.02.00viaconventionalpath　　importdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset　　importserverusesZHS16GBKcharacterset(possiblecharsetconversion)　　.importingSCOTT'sobjectsintoSCOTT　　.importingSCOTT'sobjectsintoSCOTT　　..importingpartition"TB_PT":"SAL_11"298rowsimported　　..importingpartition"TB_PT":"SAL_12"366rowsimported　　..importingpartition"TB_PT":"SAL_13"365rowsimported　　..importingpartition"TB_PT":"SAL_14"365rowsimported　　..importingpartition"TB_PT":"SAL_15"365rowsimported　　..importingpartition"TB_PT":"SAL_16"366rowsimported　　..importingpartition"TB_PT":"SAL_OTHER"2875rowsimported　　Importterminatedsuccessfullywithoutwarnings.　　SQL>selectcount(1)fromtb_ptpartition(sal_other);　　COUNT(1)　　----------　　2875　　四、使用expdp/impdb来实现分区表的导入导出　　1.查看导入导出的目录设置　　SQL>selectdirectory_name,directory_pathfromdba_directorieswheredirectory_name='DMP';　　DIRECTORY_NAMEDIRECTORY_PATH17 　　------------------------------------------------------------------------------------------　　DMP/u02/dmp　　2.为分区表创建一个本地索引　　createindextb_pt_local_idx　　ontb_pt(sal_id)　　local　　(partitionlocal1,　　partitionlocal2,　　partitionlocal3,　　partitionlocal4,　　partitionlocal5,　　partitionlocal6,　　partitionlocal7)　　;　　3.导出整个表　　[oracle@node1~]$expdpscott/tigerdirectory=dmpdumpfile=tb_pt.dmplogfile=tb_pb.logtables=tb_ptparallel=3　　Export:Release11.2.0.1.0-ProductiononWedMar914:04:282011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingoptions　　Starting"SCOTT"."SYS_EXPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pt.dmplogfile=tb_pb.logtables=　　tb_ptparallel=317 　　EstimateinprogressusingBLOCKSmethod...　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA　　TotalestimationusingBLOCKSmethod:512KB　　..exported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rows　　..exported"SCOTT"."TB_PT":"SAL_11"12.54KB298rows　　..exported"SCOTT"."TB_PT":"SAL_12"14.22KB366rows　　..exported"SCOTT"."TB_PT":"SAL_13"14.18KB365rows　　..exported"SCOTT"."TB_PT":"SAL_14"14.18KB365rows　　..exported"SCOTT"."TB_PT":"SAL_15"14.19KB365rows　　..exported"SCOTT"."TB_PT":"SAL_16"14.23KB366rows　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE　　ProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEX　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS　　ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS　　Mastertable"SCOTT"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded　　******************************************************************************　　DumpfilesetforSCOTT.SYS_EXPORT_TABLE_01is:　　/u02/dmp/tb_pt.dmp　　Job"SCOTT"."SYS_EXPORT_TABLE_01"successfullycompletedat14:04:51　　4.导出多个分区　　[oracle@node1~]$expdpscott/tigerdirectory=dmpdumpfile=tb_pts.dmplogfile=tb_pt.log/　　>tables=(tb_pt:sal_16,tb_pt:sal_other)parallel=2　　Export:Release11.2.0.1.0-ProductiononWedMar914:08:06201117 　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingoptions　　Starting"SCOTT"."SYS_EXPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pts.dmplogfile=tb_pt.log　　tables=(tb_pt:sal_16,tb_pt:sal_other)parallel=2--*/　　EstimateinprogressusingBLOCKSmethod...　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA　　TotalestimationusingBLOCKSmethod:192KB　　..exported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rows　　..exported"SCOTT"."TB_PT":"SAL_16"14.23KB366rows　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE　　ProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEX　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS　　ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS　　Mastertable"SCOTT"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded　　******************************************************************************　　DumpfilesetforSCOTT.SYS_EXPORT_TABLE_01is:　　/u02/dmp/tb_pts.dmp　　Job"SCOTT"."SYS_EXPORT_TABLE_01"successfullycompletedat14:08:17　　5.截断分区sal_other17 　　SQL>altertabletb_pttruncatepartition(sal_other);　　Tabletruncated.　　SQL>selectcount(*)fromtb_ptpartition(sal_other);　　COUNT(*)　　----------　　0　　SQL>selectindex_name,status,partitionedfromdba_indexeswheretable_name='TB_PT';--查看索引的状态,TB_PT_IND1不可用　　INDEX_NAMESTATUSPAR　　-----------------------------------------　　TB_PT_IND1UNUSABLENO　　TB_PT_LOCAL_IDXN/AYES　　SQL>selectindex_name,partition_name,statusfromdba_ind_partitionswhereindex_owner='SCOTT';　　INDEX_NAMEPARTITION_NAMESTATUS　　--------------------------------------------------------------------　　TB_PT_LOCAL_IDXLOCAL1USABLE　　TB_PT_LOCAL_IDXLOCAL2USABLE　　TB_PT_LOCAL_IDXLOCAL3USABLE　　TB_PT_LOCAL_IDXLOCAL4USABLE　　TB_PT_LOCAL_IDXLOCAL5USABLE　　TB_PT_LOCAL_IDXLOCAL6USABLE　　TB_PT_LOCAL_IDXLOCAL7USABLE　　6.导入单个分区　　[oracle@node1~]$impdpscott/tigerdirectory=dmpdumpfile=tb_pts.dmplogfile=tb_pt_imp.log/　　>tables=tb_pt:sal_otherskip_unusable_indexes=ytable_exists_action=replace17 　　Import:Release11.2.0.1.0-ProductiononWedMar914:13:282011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingoptions　　Mastertable"SCOTT"."SYS_IMPORT_TABLE_01"successfullyloaded/unloaded　　Starting"SCOTT"."SYS_IMPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pts.dmplogfile=tb_pt_imp.log　　tables=tb_pt:sal_otherskip_unusable_indexes=ytable_exists_action=replace--*/　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA　　..imported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rows　　ProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEX　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS　　ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS　　Job"SCOTT"."SYS_IMPORT_TABLE_01"successfullycompletedat14:13:33　　SQL>selectindex_name,status,partitionedfromdba_indexeswheretable_name='TB_PT';　　INDEX_NAMESTATUSPAR　　-----------------------------------------　　TB_PT_IND1VALIDNO　　TB_PT_LOCAL_IDXN/AYES17 　　从上面的导入情况可以看出，尽管执行了truncatepartition，然而使用impdp导入工具，并且使用参数table_exists_action=replace　　可以避免使用imp导入时唯一和主键索引需要重建的问题。注意，如果没有使用table_exists_action=replace参数，将会收到ORA-39151　　错误，如下　　ORA-39151:Table"SCOTT"."TB_PT"exists.Alldependentmetadataanddatawillbeskippeddueto　　table_exists_actionofskip　　7.导入整个表　　[oracle@node1~]$impdpscott/tigerdirectory=dmpdumpfile=tb_pt.dmplogfile=tb_pt_fullimp.log/　　>tables=tb_ptskip_unusable_indexes=ytable_exists_action=replace　　Import:Release11.2.0.1.0-ProductiononWedMar914:17:352011　　Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.　　Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production　　WiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,　　DataMiningandRealApplicationTestingoptions　　Mastertable"SCOTT"."SYS_IMPORT_TABLE_01"successfullyloaded/unloaded　　Starting"SCOTT"."SYS_IMPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pt.dmplogfile=tb_pt_fullimp.log　　tables=tb_ptskip_unusable_indexes=ytable_exists_action=replace--*/　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE　　ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA　　..imported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rows　　..imported"SCOTT"."TB_PT":"SAL_11"12.54KB298rows17 　　..imported"SCOTT"."TB_PT":"SAL_12"14.22KB366rows　　..imported"SCOTT"."TB_PT":"SAL_13"14.18KB365rows　　..imported"SCOTT"."TB_PT":"SAL_14"14.18KB365rows　　..imported"SCOTT"."TB_PT":"SAL_15"14.19KB365rows　　..imported"SCOTT"."TB_PT":"SAL_16"14.23KB366rows　　ProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEX　　ProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS　　ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS　　Job"SCOTT"."SYS_IMPORT_TABLE_01"successfullycompletedat14:17:40　　五、参数skip_unusable_indexes的作用　　SQL>showparameterskip　　NAMETYPEVALUE　　-----------------------------------------------------------------------------　　skip_unusable_indexesbooleanTRUE　　该参数允许在导入分区数据时延迟对索引的处理，即先将数据导入，导入后再来重建索引分区。　　在命令行导入中未指定导入参数skip_unusable_indexes时，则对于索引相关的问题，根据数据库初始化参数的值来确定。　　在命令行导入中如果指定了参数skip_unusable_indexes时，则该参数的值优先于数据库初始化参数的设定值。　　skip_unusable_indexes=y对uniqueindex不起作用，因为此时的uniqueindex扮演者constraint的作用，所以在insert数据时index必须被　　更新。　　对于单个分区导入时PK，uniqueindex的处理，必须先重建索引然后进行导入。17 　　使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题，即ORA-01502错误。17