指定一个分区除了使用分区名称外，很多时候还可以使用FOR语句。


这篇描述测试FOR语句时碰到的ORA-14702错误。






仍然使用上一篇文章的例子：


SQL> CREATE TABLE T_PART_RANGE


 2  (ID NUMBER,


 3  NAME VARCHAR2(30),


 4  CREATE_DATE DATE)


 5  PARTITION BY RANGE (CREATE_DATE)


 6  (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-1', 'YYYY-MM')),


 7  PARTITION P2 VALUES LESS THAN (TO_DATE('2009-4', 'YYYY-MM')),


 8  PARTITION P3 VALUES LESS THAN (TO_DATE('2009-7', 'YYYY-MM')));


表已创建。


下面打算通过FOR语句的方式合并P2和P3分区：


SQL> ALTER TABLE T_PART_RANGE


 2  MERGE PARTITIONS


 3  FOR(TO_DATE('2009-4', 'YYYY-MM')),


 4  FOR(TO_DATE('2009-7', 'YYYY-MM'))


 5  INTO PARTITION P3;


ALTER TABLE T_PART_RANGE


           *


第1行出现错误:


ORA-14702:分区编号无效或超出范围


语句出现了ORA-14702错误，查询Oracle的错误文档：


ORA-14702: The partition number is invalid or out-of-range


Cause:Attempted to use nonnumerical value or the number was out of range of the partitions.


Action:Use a valid partition number.


根据错误文档的描述，感觉是分区键值指定出现了错误，查询分区信息：


SQL> SELECT PARTITION_NAME, HIGH_VALUE    


 2  FROM USER_TAB_PARTITIONS


 3  WHERE TABLE_NAME = 'T_PART_RANGE'


 4  ORDER BY 1;


PARTITION_NAME HIGH_VALUE


-------------- ----------------------------------------------------------------------------------


P1             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


P2             TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


P3             TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


难道是分区键值指定有问题：


SQL> ALTER TABLE T_PART_RANGE


 2  MERGE PARTITIONS


 3  FOR(TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),


 4  FOR(TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))


 5  INTO PARTITION P3;


ALTER TABLE T_PART_RANGE


           *


第1行出现错误:


ORA-14702:分区编号无效或超出范围


完全仿照USER_TAB_PARTITIONS视图中的分区定义，错误依旧。


最终发现了问题所在，FOR语句中指定的并不是分区定义时使用的值，而是存储在当前分区中的值：


SQL> ALTER TABLE T_PART_RANGE


 2  MERGE PARTITIONS


 3  FOR(TO_DATE('2009-1', 'YYYY-MM')),


 4  FOR(TO_DATE('2009-4', 'YYYY-MM'));


表已更改。


SQL> SELECT PARTITION_NAME, HIGH_VALUE


 2  FROM USER_TAB_PARTITIONS


 3  WHERE TABLE_NAME = 'T_PART_RANGE'


 4  ORDER BY 1;


PARTITION_NAME  HIGH_VALUE


--------------- ---------------------------------------------------------------------------------


P1              TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


SYS_P78         TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


可以看到，在使用FOR语句的时候，是根据P1和P2分区定义时的日期指定的分区，但是MERGE的结果却是P2和P3分区进行了合并。


因此Oracle并非根据分区定义来判断分区，而是根据用户给出的值，来判断所属分区，所以，P1分区和SYS_P78分区的合并完全可以写成：


SQL> ALTER TABLE T_PART_RANGE


 2  MERGE PARTITIONS


 3  FOR(TO_DATE('1970-1', 'YYYY-MM')),


 4  FOR(TO_DATE('2009-5', 'YYYY-MM'));


表已更改。


由于FOR语句的这种特性，使得HASH分区也可以使用这个特性：


SQL> CREATE TABLE T_PART_HASH


 2  (ID NUMBER,


 3  NAME VARCHAR2(30))


 4  PARTITION BY HASH(ID)


 5  PARTITIONS 16;


表已创建。


SQL> ALTER TABLE T_PART_HASH


 2  MOVE PARTITION FOR(6);


表已更改。


这个例子对包含ID为6的分区进行了MOVE操作，而且甚至不需要指定的ID存在。


最后给一个简单的LIST分区的SPLIT的例子：


SQL> CREATE TABLE T_PART_LIST


 2  (


 3     OWNER VARCHAR2(30),


 4     TABLE_NAME VARCHAR2(30),


 5     TABLESPACE_NAME VARCHAR2(30),


 6     STATUS VARCHAR2(18)


 7  )


 8  PARTITION BY LIST (TABLESPACE_NAME)


 9  (


10  PARTITION P1 VALUES ('SYSTEM'),


11  PARTITION P2 VALUES ('YANGTK'),


12  PARTITION P3 VALUES (DEFAULT)


13  );


表已创建。


SQL> ALTER TABLE T_PART_LIST


 2  SPLIT PARTITION FOR('SYSAUX')


 3  VALUES ('SYSAUX')


 4  INTO (PARTITION P3, PARTITION P4);


表已更改。


SQL> SELECT PARTITION_NAME, HIGH_VALUE


 2  FROM USER_TAB_PARTITIONS


 3  WHERE TABLE_NAME = 'T_PART_LIST'


 4  ORDER BY 1;


PARTITION_NAME  HIGH_VALUE


--------------- -------------------------------------


P1              'SYSTEM'


P2              'YANGTK'


P3              'SYSAUX'


P4              DEFAULT


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html