测试环境中对拥有maxvalue分区的范围分区表,新加分区时报如下错误:
ORA-14074: partition bound must collate higher than that of the last partition
此情况下,可以通过ALTER TABLE ... SPLIT PARTITION命令新加分区,以下给出测试步骤
1、新建分区表
CREATE TABLE sales
( prod_id NUMBER(6)
, time_id DATE
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy'))
TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_maxvalue VALUES LESS THAN (maxvalue)
TABLESPACE tsc
);
2、查看分区情况
SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='SALES';
PARTITION_NAME HIGH_VALUE
-------------------- ------------------------------------------------------------
SALES_MAXVALUE MAXVALUE
SALES_Q1_2006 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_Q2_2006 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
3、新增一个分区
1)ALTER TABLE ... ADD PARTITION方式报错
SQL> alter table sales add partition sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')) tablespace tsd;
alter table sales add partition sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')) tablespace tsd
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
2)ALTER TABLE ... SPLIT PARTITION方式
SQL> alter table sales split partition sales_maxvalue at ('01-OCT-2016') INTO (partition sales_q3_2016 tablespace tsd,PARTITION sales_maxvalue);
Table altered.
3)查看分区情况,新的分区已经能够看到
SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------
SALES_Q1_2006 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_Q2_2006 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_Q3_2016 TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_MAXVALUE MAXVALUE
总结:
1、针对有maxvalue分区的范围分区表,可以使用ALTER TABLE ... SPLIT PARTITION方式来新增分区。
2、在本次测试中split出的新的分区是空的,所以 global indexes和local index都是可用的。
ORA-14074: partition bound must collate higher than that of the last partition
此情况下,可以通过ALTER TABLE ... SPLIT PARTITION命令新加分区,以下给出测试步骤
1、新建分区表
CREATE TABLE sales
( prod_id NUMBER(6)
, time_id DATE
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy'))
TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_maxvalue VALUES LESS THAN (maxvalue)
TABLESPACE tsc
);
2、查看分区情况
SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='SALES';
PARTITION_NAME HIGH_VALUE
-------------------- ------------------------------------------------------------
SALES_MAXVALUE MAXVALUE
SALES_Q1_2006 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_Q2_2006 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
3、新增一个分区
1)ALTER TABLE ... ADD PARTITION方式报错
SQL> alter table sales add partition sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')) tablespace tsd;
alter table sales add partition sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')) tablespace tsd
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
2)ALTER TABLE ... SPLIT PARTITION方式
SQL> alter table sales split partition sales_maxvalue at ('01-OCT-2016') INTO (partition sales_q3_2016 tablespace tsd,PARTITION sales_maxvalue);
Table altered.
3)查看分区情况,新的分区已经能够看到
SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------
SALES_Q1_2006 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_Q2_2006 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_Q3_2016 TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES_MAXVALUE MAXVALUE
总结:
1、针对有maxvalue分区的范围分区表,可以使用ALTER TABLE ... SPLIT PARTITION方式来新增分区。
2、在本次测试中split出的新的分区是空的,所以 global indexes和local index都是可用的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25923810/viewspace-2121209/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25923810/viewspace-2121209/