分区表述的FOR语句（二）

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

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

分区表述的FOR语句（一）：http://yangtingkun.itpub.net/post/468/497173

 

 

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

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

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-628640/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-628640/