Oracle分割槽資料問題的分析和修復

今天根據同事的反饋，處理了一個分割槽表的問題，也讓我對Oracle的分割槽表功能有了進一步的理解。

  首先根據開發同事的反饋，他們在程式批次插入一部分資料的時候，總是會有一部分請求執行失敗，而檢視日誌就是ORA-14400的錯誤，對於這類問題，我有一個很直觀的感覺，分割槽有問題。

> INSERT INTO DY_USER_ANALYSIS_MIN(ID,STAT_TIME,GAME_TYPE,ZONE_ID,GROUP_ID,ONLINE_5CNT)
    VALUES(100,to_date('2017-07-12 17:40:00','yyyy-mm-dd HH24:mi:ss'),'pz',to_number(-1),to_number(-1),to_number(0));
INSERT INTO DY_USER_ANALYSIS_MIN(ID,STAT_TIME,GAME_TYPE,ZONE_ID,GROUP_ID,ONLINE_5CNT)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

而如果把‘pz’修改為另外一個字串'dhsh'就沒問題。

  所以這樣一個ORA問題，透過初始資訊我得到一個基本的推論，那就是沒有符合條件的分割槽了。而如果仔細分析，會發現這個問題似乎有些蹊蹺。

   一般的分割槽表都是Range分割槽，基本就是數值範圍或者是日期來做範圍分割槽，這個問題該怎麼理解呢，如果按照時間分割槽，那麼另外一個SQL插入也應該失敗才對。

   所以帶著疑惑，我檢視了分割槽的情況，發現這個表竟然有預設鍵值maxvlue的分割槽，所以如果說指定的Range分割槽不存在，似乎有些說不通。

   這個問題該如果解決呢，一個直觀的地方就是檢視錶的DDL,dbms_metadata.get_ddl即可得到。

   得到的DDL一看，我就有些懵了，開發同學怎麼知道這個list分割槽，竟然已經用上了這個還算高階的特性吧，就是Range-list分割槽。

PARTITION BY RANGE ("STAT_TIME")
  SUBPARTITION BY LIST ("GAME_TYPE")
  SUBPARTITION TEMPLATE (
    SUBPARTITION "SP_ABC" values ( 'abc' )
  TABLESPACE "TEST_DATA" ,
。。。
    SUBPARTITION "SP_OTHER" values ( 'xjzj', 'hij'
)   TABLESPACE "TEST_DATA"  )
 (PARTITION "P_OLD"  VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

 對於這類問題，雖然還是有些陌生，但是還是有一些分割槽表的底子的，所以分析起來也不會有太大的偏差。

 按照DDL的格式，我們是要想修改template的子分割槽模板規則。

alter table TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN
set SUBPARTITION TEMPLATE (
    SUBPARTITION "SP_ABC" values ( 'abc' )
  TABLESPACE "TEST_DATA" ,
    。。。
    SUBPARTITION "SP_OTHER" values ( 'xjzj', 'hij','pz’)
  TABLESPACE "TEST_DATA"  )

按照這種方式修改模板就沒有問題了，然後繼續嘗試插入資料，發現還是同樣的錯誤。這個時候是哪裡的問題了呢。

   根據錯誤反覆排查，還是指向了分割槽的定義，那麼我們看看其中一個分割槽的情況。

 (PARTITION "P_OLD"  VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL
S_CALENDAR=GREGORIAN'))
  TABLESPACE "TEST_DATA"
 ( SUBPARTITION "P_OLD_SP_ABC"  VALUES ('abc')
   TABLESPACE "TEST_DATA",
 。。。
  SUBPARTITION "P_OLD_SP_OTHER"  VALUES ('xjzj', hij', 'pz')
   TABLESPACE "TEST_DATA") ,

所以按照分割槽的定義，裡面還是少了這個subpartition的數值範圍資訊。

如果想重新生成一個新的subpartition可以使用如下的方式：

ALTER TABLE TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN MODIFY PARTITION P_OLD add SUBPARTITION P_OLD_SP_OTHER_pz VALUES ('pz');  

  如果想生成預設的subpartition名稱可以使用如下的方式：

ALTER TABLE TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN MODIFY PARTITION P2017_Q2 add SUBPARTITION  VALUES ('pz');    

這個時候的subpartition的資訊，我摘錄出一個來簡單看看。

 ( SUBPARTITION "P2017_Q3_SP_ABC"  VALUES ('abc')
   TABLESPACE "TEST_DATA",
 。。。
  SUBPARTITION "P2017_Q3_SP_OTHER"  VALUES ('xjzj', 'hij')     TABLESPACE "TEST_DATA",
  SUBPARTITION "SYS_SUBP22"  VALUES ('pz')
   TABLESPACE "TEST_DATA") ,

如果依舊覺得不滿意，我們來使用merge subpartitions的方式，當然這個操作還是會有全域性鎖的，會把兩個分割槽整合為一個。

ALTER TABLE TLSTAT_NEWBG.DY_USER_ANALYSIS_MIN MERGE SUBPARTITIONS P2017_Q2_SP_OTHER,SYS_SUBP21 INTO SUBPARTITION P2017_Q2_SP_OTHER;