开发者社区> 问答> 正文

ORACLE 分区后dmp导入报错:imp-00058 遇到oracle错误 14400?报错

oralce数据库表分区创建后,在导入dmp文件时报错:

imp-00058 遇到oracle错误 14400

ORA-14400: inserted partition key does not map to any partition

自己百度了一下,貌似是导入的数据找不到适合自己的分区。

表分区创建的代码:

create table ZB_FXDATA
(
  YEARS            VARCHAR2(40),
  MONTHS           VARCHAR2(40),
  C_UNITNO         VARCHAR2(100),
  C_UNITSHORTNAME  VARCHAR2(50),
  C_CLASSNO        VARCHAR2(20),
  C_BUSINESSNAME   VARCHAR2(30),
  ZB_ACCOUNTNO     VARCHAR2(15),
  ZB_ACCOUNTNAME   VARCHAR2(255),
  N_YEARTOTAL      NUMBER,
  N_UPYEARTOTAL    NUMBER,
  N_MONTHAMOUNT    NUMBER,
  N_UPMONTHAMOUNT  NUMBER,
  C_BUSINESSNO     VARCHAR2(3),
  N_YEARTOTAL_SR   NUMBER,
  N_MONTHAMOUNT_SR NUMBER
)
 partition by range (MONTHS)
   (partition ZB_FXDATA_PT_09 values  less than ('10')   tablespace MONTH09,
   partition ZB_FXDATA_PT_10 values  less than ('11')   tablespace MONTH10,
   partition ZB_FXDATA_PT_11 values  less than ('12')   tablespace MONTH11,
   partition ZB_FXDATA_PT_12 values  less than ('13')   tablespace MONTH12,
   partition ZB_FXDATA_PT_01 values  less than ('2')   tablespace MONTH01,
   partition ZB_FXDATA_PT_02 values  less than ('3')   tablespace MONTH02,
   partition ZB_FXDATA_PT_03 values  less than ('4')   tablespace MONTH03,
   partition ZB_FXDATA_PT_04 values  less than ('5')   tablespace MONTH04,
   partition ZB_FXDATA_PT_05 values  less than ('6')   tablespace MONTH05,
   partition ZB_FXDATA_PT_06 values  less than ('7')   tablespace MONTH06,
   partition ZB_FXDATA_PT_07 values  less than ('8')   tablespace MONTH07,
   partition ZB_FXDATA_PT_08 values  less than ('9')   tablespace MONTH08
   );



我是按照月份为分区的关键字进行的分区,1-12月。月份是个字符类型的。

我也参考了网上的一篇文章:http://blog.itpub.net/15112866/viewspace-442689/

里面也提到了一种开启 ROW MOVEMENT方法,我尝试了不行,应该不是这个问题。

既然是找不到合适自己的分区,我觉得应该是less than后面的问题,请大家指教一下!


展开
收起
爱吃鱼的程序员 2020-06-14 22:10:40 1146 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB

    已解决:

    分区代码:

    createtableGLKJ_DATASZICHAN
    (
     YEARS     VARCHAR2(4),
     MONTHS     VARCHAR2(4),
     C_KJCLASS   VARCHAR2(50),
     C_UNITNO    VARCHAR2(100),
     C_UNITSHORTNAMEVARCHAR2(50),
     C_CLASSNO   VARCHAR2(20),
     C_BUSINESSNAME VARCHAR2(30),
     N_NUMBER    NUMBER,
     C_ACCOUNTNO  VARCHAR2(20),
     C_ACCOUNTNAME VARCHAR2(200),
     N_YEARTOTAL  NUMBER,
     N_UPYEARTOTAL NUMBER,
     N_MONTHAMOUNT NUMBER,
     N_UPMONTHAMOUNTNUMBER,
     C_BUSINESSNO  VARCHAR2(3),
     C_KJCLASSNO  VARCHAR2(3),
     C_DATATYPE   VARCHAR2(3)
    )
      partitionbyrange(MONTHS)
      (
      partitionGLKJ_DATASZICHAN_PT_01values lessthan('10') tablespaceMONTH01,
      partitionGLKJ_DATASZICHAN_PT_10values lessthan('100') tablespaceMONTH10,
      partitionGLKJ_DATASZICHAN_PT_11values lessthan('110') tablespaceMONTH11,
      partitionGLKJ_DATASZICHAN_PT_12values lessthan('120') tablespaceMONTH12,
      partitionGLKJ_DATASZICHAN_PT_02values lessthan('20') tablespaceMONTH02,
      partitionGLKJ_DATASZICHAN_PT_03values lessthan('30') tablespaceMONTH03,
      partitionGLKJ_DATASZICHAN_PT_04values lessthan('40') tablespaceMONTH04,
      partitionGLKJ_DATASZICHAN_PT_05values lessthan('50') tablespaceMONTH05,
      partitionGLKJ_DATASZICHAN_PT_06values lessthan('60') tablespaceMONTH06,
      partitionGLKJ_DATASZICHAN_PT_07values lessthan('70') tablespaceMONTH07,
      partitionGLKJ_DATASZICHAN_PT_08values lessthan('80') tablespaceMONTH08,
      partitionGLKJ_DATASZICHAN_PT_09values lessthan('90') tablespaceMONTH09,
      partitionGLKJ_DATASZICHAN_PT_13values lessthan(maxvalue) tablespaceCOGNOS_DATA
      );

    注意分区的关键字也就是lessthan后面的值一定要有顺序的,否则会报错 ORA-14037 分区界限过高!

    换成list分区试试
    2020-06-14 22:10:58
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践 立即下载
PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移 立即下载
Oracle云上最佳实践 立即下载

相关镜像