ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表

startay發表於2016-01-08
Oracle
ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表

INTERVAL分割槽其實是一種比較特殊的範圍分割槽，因此可以很方便的將RANGE分割槽錶轉化為INTERVAL分割槽表，同樣可以將INTERVAL分割槽錶轉化為RANGE分割槽表。

對於一個普通的時間範圍分割槽表：
  1. 1. 建立分割槽表
  2. CREATE TABLE T_PART3(ID NUMBER,NAME VARCHAR2(30),CREATE_DATE TIMESTAMP)
  3.   PARTITION BY RANGE (CREATE_DATE)
  4. (PARTITION P3_1 VALUES LESS THAN (TIMESTAMP' 2016-01-01 00:00:00') ,
  5.  PARTITION P3_2 VALUES LESS THAN (TIMESTAMP' 2016-02-01 00:00:00') ,
  6.  PARTITION P3_3 VALUES LESS THAN (TIMESTAMP' 2016-03-01 00:00:00')
  7.  );

 2. 隨機插入資料
  1. insert into T_PART3
  2. select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <1000;
--超出分割槽範圍
 ERROR at line 1:
 ORA-14400: inserted partition key does not map to any partition
 
--修改語句，繼續插入
  1. insert into T_PART3 
  2. select rownum*10,'N'||level,sysdate+dbms_random.value(0,50) from dual connect by level <1000;

  3. commit;

  5. exec dbms_stats.gather_table_stats('','T_PART3');

  7. PL/SQL procedure successfully completed.

  9. 3. 檢視分割槽資料分部情況
  10. select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART3';

  12. TABLE_OWNER          TABLE_NAME                       PARTITION_NAME       PARTITION_POSITION INTERV   NUM_ROWS HIGH_VALUE
    -------------------- -------------------------------- -------------------- ------------------ ------ ---------- -----------------------------------
    IBM                  T_PART3                          P3_1                                  1 NO              0 TIMESTAMP' 2016-01-01 00:00:00'
    IBM                  T_PART3                          P3_2                                  2 NO            452 TIMESTAMP' 2016-02-01 00:00:00'
    IBM                  T_PART3                          P3_3                                  3 NO            547 TIMESTAMP' 2016-03-01 00:00:00'

  13. 4. 轉換為按月的INTERVAL分割槽表
  14. ALTER TABLE T_PART3 SET INTERVAL (NUMTOYMINTERVAL (1,'MONTH'));

 5. 加大範圍，插入資料
  1. insert into T_PART3 
  2. select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <2000;

 6. 檢視分割槽資料分部情況
   exec dbms_stats.gather_table_stats('','T_PART3');
  1. select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART3';

  2.
  3. TABLE_OWNER          TABLE_NAME                       PARTITION_NAME       PARTITION_POSITION INTERV   NUM_ROWS HIGH_VALUE
    -------------------- -------------------------------- -------------------- ------------------ ------ ---------- -----------------------------------
    IBM                  T_PART3                          P3_1                                  1 NO              0 TIMESTAMP' 2016-01-01 00:00:00'
    IBM                  T_PART3                          P3_2                                  2 NO            650 TIMESTAMP' 2016-02-01 00:00:00'
    IBM                  T_PART3                          P3_3                                  3 NO            760 TIMESTAMP' 2016-03-01 00:00:00'
    IBM                  T_PART3                          SYS_P53133                            4 YES           245 TIMESTAMP' 2016-04-01 00:00:00'
    IBM                  T_PART3                          SYS_P53134                            5 YES           267 TIMESTAMP' 2016-05-01 00:00:00'
    IBM                  T_PART3                          SYS_P53130                            6 YES           261 TIMESTAMP' 2016-06-01 00:00:00'
    IBM                  T_PART3                          SYS_P53132                            7 YES           243 TIMESTAMP' 2016-07-01 00:00:00'
    IBM                  T_PART3                          SYS_P53131                            8 YES           239 TIMESTAMP' 2016-08-01 00:00:00'
    IBM                  T_PART3                          SYS_P53135                            9 YES           238 TIMESTAMP' 2016-09-01 00:00:00'
    IBM                  T_PART3                          SYS_P53136                           10 YES            95 TIMESTAMP' 2016-10-01 00:00:00'

  1. 7. INTERVAL分割槽錶轉換未正常的RANGE分割槽
  2. ALTER TABLE T_PART3 SET INTERVAL ();



若分割槽表存在maxvalue分割槽，則不能直接轉換為INTERVAL分割槽，需要先將maxvalue分割槽刪除才可轉換。

  1. 1. 建立存在maxvalue分割槽的分割槽表
  2. CREATE TABLE T_PART4(ID NUMBER,NAME VARCHAR2(30),CREATE_DATE TIMESTAMP)
  3.   PARTITION BY RANGE (CREATE_DATE)
  4. (PARTITION P4_1 VALUES LESS THAN (TIMESTAMP' 2016-01-01 00:00:00') ,
  5.  PARTITION P4_2 VALUES LESS THAN (TIMESTAMP' 2016-02-01 00:00:00') ,
  6.  PARTITION P4_3 VALUES LESS THAN (TIMESTAMP' 2016-03-01 00:00:00') ,
  7.  PARTITION P4_4 VALUES LESS THAN (maxvalue)
  8.  );
  9.  
  10. insert into T_PART4
  11. select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <1040;


  14. exec dbms_stats.gather_table_stats('','T_PART4');

  16. 2. 查詢分割槽資料分部情況
  17. select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART4';

  18.
  19. TABLE_OWNER          TABLE_NAME                       PARTITION_NAME       PARTITION_POSITION INTERV   NUM_ROWS HIGH_VALUE
    -------------------- -------------------------------- -------------------- ------------------ ------ ---------- -----------------------------------
    IBM                  T_PART4                          P4_1                                  1 NO              0 TIMESTAMP' 2016-01-01 00:00:00'
    IBM                  T_PART4                          P4_2                                  2 NO            100 TIMESTAMP' 2016-02-01 00:00:00'
    IBM                  T_PART4                          P4_3                                  3 NO            126 TIMESTAMP' 2016-03-01 00:00:00'
    IBM                  T_PART4                          P4_4                                  4 NO            813 MAXVALUE

  20. 3. 轉換為按月的INTERVAL分割槽表
  21. ALTER TABLE T_PART4 SET INTERVAL (NUMTOYMINTERVAL (1,'MONTH'));
 
 ERROR at line 1:
 ORA-14759: SET INTERVAL is not legal on this table.
 
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 Message:
 ORA-14759: SET INTERVAL is not legal on this table.

 Cause:
 ALTER TABLE SET INTERVAL is only legal on a range partitioned table with a single partitioning column. Additionally this table cannot have a maxvalue partition.

 Action:
 Use SET INTERVAL only on a valid table
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 





 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17086096/viewspace-1973998/，如需轉載，請註明出處，否則將追究法律責任。

相關文章