资源描述：
《创建 导入导出 oracle 分区表数据》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
导入导出Oracle分区表数据Createtable(创建分区表) createtableBILL_MONTHFEE_ZERO ( SERV_ID NUMBER(20)notnull, BILLING_CYCLE_MONTHNUMBER(6)notnull, DATE_TYPE NUMBER(1), ACC_NBR VARCHAR2(80) ) partitionbyrange(BILLING_CYCLE_MONTH) (partitionp_200407valueslessthan(200407) tablespaceTS_ZIKEN storage(initial100knext100kminextents1maxextentsunlimitedpctincrease0), partitionp_200408valueslessthan(200408) tablespaceTS_ZIKEN storage(initial100knext100kminextents1maxextentsunlimitedpctincrease0)) ; createindexidx_bill_monthfee_zero_idx01onbill_monthfee_zero(billing_cycle_month) tablespaceTS_ZIKEN_idx storage(initial100knext100kminextents1maxextentsunlimitedpctincrease0)nologging; grantallonbill_monthfee_zerotodxsq_dev; --增加分区表 altertableBILL_MONTHFEE_ZEROaddPartitionp_200409 valueslessthan(200409)tablespacets_ziken; --删除一分区 altertablepart_tbldropPartitionpart_tbl_08; --将一个分区分为两个分区 altertablebill_monthfee_zerosplitPartitionp_200409at(200409) into(Partitionp_200409_1tablespacets_ziken, Partitionp_200409_2tablespacets_ziken_idx); --合并分区 ALTERTABLEbill_monthfee_zero MERGEPARTITIONSp_200408,p_200409INTOPARTITIONp_all --将分区改名 altertablebill_monthfee_zerorenamePartitionp_200408top_fee_200408 --将分区改表空间 altertablebill_monthfee_zeromovePartitionp_200409 tablespacets_ziken_01nologging --查询特定分区 selectcount(*)fromBILL_MONTHFEE_ZEROpartition(p_200407); --添加数据 insertintobill_monthfee_zeroselect*frombill_monthfee_zeropartition(p_200407) --分区表的导出 userid=dxsq/teledoone@jndxsq154 buffer=102400 tables=bill_monthfee:P_200401, file=E:exp_paraexp_dxsq_tables.dmp log=E:exp_paraexp_dxsq_tables.log 技巧： 删除表中一个字段： altertablebill_monthfee_zerosetunusedcolumndate_type; 添加一个字段：altertablebill_monthfee_zeroadddate_typenumber(1);UNIX中ORACLE分区表导出创建分区表：createtabletest(join_dateVARCHAR2(8),nameVARCHAR2(50),sexchar(1),addressvarchar2(100))partitionbylist(statis_date)(partitionpart_20091101values('20091101'));转移分区表altertabletb_cust_view_daymovePartitionPART_20100505tablespaceTBS_USERnologging;转移表空间，将该表20100505的分区转移至表空间TBS_USER，这样他就占用的是USER的空间altertableTB_CUST_INFO_DAYdroppartitionPART_20100301;删除表分区altertabletbnamedroppartitionpartnamealtertabletbnamemovepartitionpartnametablespacetbspacename1.分区表导出--单表导出 exptest/testfile=/home/wtest/mail_cust12.dmptables=PERSON_MAIL_MON:PART_200912--多表导出expfile=part_1.dmpuserid=test/test@orclbuffer=33554432recordlength=65535tables=TABLE_PART1:PART_20091216TABLE_PART12:PART_200912162.普通表多表导出---月表：导出201001的数据expfile=tbale12userid=test/test@orclbuffer=33554432recordlength=65535tables=table_Atable_Bquery="whereDATE_MONTH='201001'"---日表导出20100218--20100220的数据expfile=/home/test/table15_16.dmpuserid=test/test@orclbuffer=33554432recordlength=65535tables=table_aatable_bbquery="wheredate_daybetween'20100218'and'20100220'"关键字说明(默认)userid用户名/口令buffer数据缓冲区大小file输出文件(EXPDAT.DMP)compress导入到一个区(Y)grants导出权限(Y)indexes导出索引(Y)direct直接路径(N)log屏幕输出的日志文件rows导出数据行(Y)consistent交叉表的一致性(N)full导出整个文件(N)owner所有者用户名列表tables表名称列表recordlengthIO记录的长度inctype增量导出类型record跟踪增量导出(Y)triggers导出触发器(Y)statistics分析对象(ESTIMATE)parfile参数文件名constraints导出的约束条件(Y)object_consistent只在对象导出期间设置为读的事务处理(N) feedback每x行的显示进度(0)filesize每个转储文件的最大大小flashback_scn用于将会话快照设置回以前状态的SCNflashback_time用于获取最接近指定时间的SCN的时间query用于导出表的子集的select子句resumable遇到与空格相关的错误时挂起(N)resumable_name用于标识可恢复语句的文本字符串resumable_timeoutRESUMABLE的等待时间tts_full_check对TTS执行完整的或部分相关性检查tablespaces要导出的表空间列表transport_tablespace导出可传输的表空间元数据(N)template调用iAS模式导出的模板名注意：unix中oracle多表用空格区分--导入导出Oracle分区表数据--****************************导入导入Oracle分区表数据是OracleDBA经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出分区表数据。 有关分区表的特性请参考：Oracle分区表SQLserver2005切换分区表SQLserver2005基于已存在的表创建分区 有关导入导出工具请参考：数据泵EXPDP导出工具的使用数据泵IMPDP导入工具的使用 有关导入导出的官方文档请参考：OriginalExportandImport 一、分区级别的导入导出可以导出一个或多个分区，也可以导出所有分区(即整个表)。 可以导入所有分区(即整个表)，一个或多个分区以及子分区。对于已经存在数据的表，使用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)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_ind1ONtb_pt(sal_date)nologging; 4.为分区表生成数据SQL>INSERTINTOtb_ptSELECTTRUNC(SYSDATE)+ROWNUM,dbms_random.random,ROWNUMFROMdualCONNECTBYLEVEL<=5000; SQL>commit; SQL>selectcount(1)fromtb_ptpartition(sal_11); COUNT(1)----------300 SQL>selectcount(1)fromtb_ptpartition(sal_other); COUNT(1)----------2873 SQL>select*fromtb_ptpartition(sal_12)whererownum<3; SAL_DATESAL_IDSAL_ROW-----------------------------01-JAN-12-1.356E+0930102-JAN-12-761530183302 三、使用exp/imp导出导入分区表数据1.导出整个分区表[oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt.dmp'log='/u02/dmp/tb_pt.log'tables=tb_ptExport:Release11.2.0.1.0-ProductiononWedMar913:52:182011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved. Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoExportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetserverusesZHS16GBKcharacterset(possiblecharsetconversion)AbouttoexportspecifiedtablesviaConventionalPath.....exportingtableTB_PT..exportingpartitionSAL_11300rowsexported..exportingpartitionSAL_12366rowsexported..exportingpartitionSAL_13365rowsexported..exportingpartitionSAL_14365rowsexported..exportingpartitionSAL_15365rowsexported..exportingpartitionSAL_16366rowsexported..exportingpartitionSAL_OTHER2873rowsexportedEXP-00091:Exportingquestionablestatistics.EXP-00091:Exportingquestionablestatistics.Exportterminatedsuccessfullywithwarnings.[oracle@node1~]$oerrexp0009100091,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' 经过上述设置之后再次导出正常，过程略。 2.导出单个分区 [oracle@node1~]$expscott/tigerfile='/u02/dmp/tb_pt_sal_16.dmp'log='/u02/dmp/tb_pt_sal_16.log'tables=tb_pt:sal_16Export:Release11.2.0.1.0-ProductiononWedMar913:52:382011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoExportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset AbouttoexportspecifiedtablesviaConventionalPath.....exportingtableTB_PT..exportingpartitionSAL_16366rowsexportedEXP-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'/>file='/u02/dmp/tb_pt.dmp'ignore=yExport:Release11.2.0.1.0-ProductiononWedMar913:54:382011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoExportfilecreatedbyEXPORT:V11.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(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)----------0 SQL>hoimpscott/tigertables=tb_pt:sal_16file='/u02/dmp/tb_pt_sal_16.dmp'ignore=yExport:Release11.2.0.1.0-ProductiononWedMar913:55:392011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoExportfilecreatedbyEXPORT:V11.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(possiblecharsetconversion).importingSCOTT'sobjectsintoSCOTT.importingSCOTT'sobjectsintoSCOTT..importingpartition"TB_PT":"SAL_16"IMP-00058:ORACLEerror1502encountered ORA-01502:index'SCOTT.TB_PT_IND1'orpartitionofsuchindexisinunusablestateImportterminatedsuccessfullywithwarnings. 收到了ORA-01502错误，下面查看索引的状态，并对其重建索引后再执行导入SQL>selectindex_name,statusfromdba_indexeswheretable_name='TB_PT';--查看索引的状态 INDEX_NAMESTATUS--------------------------------------TB_PT_IND1UNUSABLE SQL>alterindexTB_PT_IND1rebuildonline;--重建索引 Indexaltered. SQL>hoimpscott/tigertables=tb_pt:sal_16file='/u02/dmp/tb_pt_sal_16.dmp'ignore=y--再次导入成功Export:Release11.2.0.1.0-ProductiononWedMar913:56:152011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoExportfilecreatedbyEXPORT:V11.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(possiblecharsetconversion).importingSCOTT'sobjectsintoSCOTT.importingSCOTT'sobjectsintoSCOTT..importingpartition"TB_PT":"SAL_16"366rowsimportedImportterminatedsuccessfullywithoutwarnings.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=yExport:Release11.2.0.1.0-ProductiononWedMar913:57:102011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoExportfilecreatedbyEXPORT:V11.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(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"2875rowsimportedImportterminatedsuccessfullywithoutwarnings.SQL>selectcount(1)fromtb_ptpartition(sal_other); COUNT(1)----------2875 四、使用expdp/impdb来实现分区表的导入导出1.查看导入导出的目录设置SQL>selectdirectory_name,directory_pathfromdba_directorieswheredirectory_name='DMP'; DIRECTORY_NAMEDIRECTORY_PATH------------------------------------------------------------------------------------------DMP/u02/dmp 2.为分区表创建一个本地索引createindextb_pt_local_idxontb_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=3Export:Release11.2.0.1.0-ProductiononWedMar914:04:282011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsStarting"SCOTT"."SYS_EXPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pt.dmplogfile=tb_pb.logtables=tb_ptparallel=3EstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod: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.23KB366rowsProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Mastertable"SCOTT"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded******************************************************************************DumpfilesetforSCOTT.SYS_EXPORT_TABLE_01is:/u02/dmp/tb_pt.dmpJob"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=2Export:Release11.2.0.1.0-ProductiononWedMar914:08:062011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsStarting"SCOTT"."SYS_EXPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pts.dmplogfile=tb_pt.logtables=(tb_pt:sal_16,tb_pt:sal_other)parallel=2--*/EstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod:192KB..exported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rows..exported"SCOTT"."TB_PT":"SAL_16"14.23KB366rowsProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSMastertable"SCOTT"."SYS_EXPORT_TABLE_01"successfullyloaded/unloaded ******************************************************************************DumpfilesetforSCOTT.SYS_EXPORT_TABLE_01is:/u02/dmp/tb_pts.dmpJob"SCOTT"."SYS_EXPORT_TABLE_01"successfullycompletedat14:08:17 5.截断分区sal_otherSQL>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_IND1UNUSABLENOTB_PT_LOCAL_IDXN/AYESSQL>selectindex_name,partition_name,statusfromdba_ind_partitionswhereindex_owner='SCOTT';INDEX_NAMEPARTITION_NAMESTATUS--------------------------------------------------------------------TB_PT_LOCAL_IDXLOCAL1USABLETB_PT_LOCAL_IDXLOCAL2USABLETB_PT_LOCAL_IDXLOCAL3USABLETB_PT_LOCAL_IDXLOCAL4USABLETB_PT_LOCAL_IDXLOCAL5USABLE TB_PT_LOCAL_IDXLOCAL6USABLETB_PT_LOCAL_IDXLOCAL7USABLE6.导入单个分区[oracle@node1~]$impdpscott/tigerdirectory=dmpdumpfile=tb_pts.dmplogfile=tb_pt_imp.log/>tables=tb_pt:sal_otherskip_unusable_indexes=ytable_exists_action=replaceImport:Release11.2.0.1.0-ProductiononWedMar914:13:282011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsMastertable"SCOTT"."SYS_IMPORT_TABLE_01"successfullyloaded/unloadedStarting"SCOTT"."SYS_IMPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pts.dmplogfile=tb_pt_imp.logtables=tb_pt:sal_otherskip_unusable_indexes=ytable_exists_action=replace--*/ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA..imported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rowsProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob"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/AYES从上面的导入情况可以看出，尽管执行了truncatepartition，然而使用impdp导入工具，并且使用参数table_exists_action=replace可以避免使用imp导入时唯一和主键索引需要重建的问题。注意，如果没有使用table_exists_action=replace参数，将会收到ORA-39151错误，如下ORA-39151:Table"SCOTT"."TB_PT"exists.Alldependentmetadataanddatawillbeskippedduetotable_exists_actionofskip 7.导入整个表[oracle@node1~]$impdpscott/tigerdirectory=dmpdumpfile=tb_pt.dmplogfile=tb_pt_fullimp.log/>tables=tb_ptskip_unusable_indexes=ytable_exists_action=replaceImport:Release11.2.0.1.0-ProductiononWedMar914:17:352011Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsMastertable"SCOTT"."SYS_IMPORT_TABLE_01"successfullyloaded/unloadedStarting"SCOTT"."SYS_IMPORT_TABLE_01":scott/********directory=dmpdumpfile=tb_pt.dmplogfile=tb_pt_fullimp.logtables=tb_ptskip_unusable_indexes=ytable_exists_action=replace--*/ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLEProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA..imported"SCOTT"."TB_PT":"SAL_OTHER"71.63KB2875rows..imported"SCOTT"."TB_PT":"SAL_11"12.54KB298rows..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.23KB366rowsProcessingobjecttypeTABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob"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的处理，必须先重建索引然后进行导入。使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题，即ORA-01502错误。
