--建表
create table TESTDB (
MESSAGEID VARCHAR2(38) not null,
STARTTIME VARCHAR2(30) not null,
constraint TESTDB_K primary key (MESSAGEID)
)
partition by range(STARTTIME)
(
partition BF110428 values less than (to_date('2011-04-28', 'yyyy-mm-dd'))
TABLESPACE gps STORAGE(initial 64k NEXT 64k),
partition P20110428 values less than (to_date('2011-04-29', 'yyyy-mm-dd'))
TABLESPACE gps STORAGE(initial 64k NEXT 64k),
partition PMAX values less than (MAXVALUE)
tablespace gps STORAGE(initial 64k NEXT 64k)
)NOLOGGING;
--查看表分区 SELECT * FROM user_tab_partitions WHERE table_name='TESTDB';结果:"TABLE_NAME""COMPOSITE""PARTITION_NAME""SUBPARTITION_COUNT""HIGH_VALUE""HIGH_VALUE_LENGTH""PARTITION_POSITION"
"TESTDB" "NO" "BF110428" 0 "to_date('2011-04-28', 'yyyy-mm-dd')" 35 1
"TESTDB" "NO" "P20110428" 0 "to_date('2011-04-29', 'yyyy-mm-dd')" 35 2
"TESTDB" "NO" "P20110429" 0 "to_date('2011-04-30','YYYY-MM-DD')" 34 3
"TESTDB" "NO" "PMAX" 0 "MAXVALUE" 8 4--拆分分区,把PMAX拆分,增加一个新的的分区。增加4月最后一天的分区报错,请问该如何处理此问题?谢谢!错误如下: alter table TESTDB
split partition PMAX at(to_date('2011-05-01','YYYY-MM-DD'))into (partition P20110430,partition PMAX); 错误报告:
SQL 错误: ORA-14080: 无法按指定的上限来分割分区
14080. 00000 - "partition cannot be split along the specified high bound"
*Cause: User attempted to split a partition along a bound which
either collates higher than that of the partition to be split or
lower than that of a partition immediately preceding the one
to be split
*Action: Ensure that the bound along which a partition is to be split
collates lower than that of the partition to be split and
higher that that of a partition immediately preceding the one
to be split
create table TESTDB (
MESSAGEID VARCHAR2(38) not null,
STARTTIME VARCHAR2(30) not null,
constraint TESTDB_K primary key (MESSAGEID)
)
partition by range(STARTTIME)
(
partition BF110428 values less than (to_date('2011-04-28', 'yyyy-mm-dd'))
TABLESPACE gps STORAGE(initial 64k NEXT 64k),
partition P20110428 values less than (to_date('2011-04-29', 'yyyy-mm-dd'))
TABLESPACE gps STORAGE(initial 64k NEXT 64k),
partition PMAX values less than (MAXVALUE)
tablespace gps STORAGE(initial 64k NEXT 64k)
)NOLOGGING;
--查看表分区 SELECT * FROM user_tab_partitions WHERE table_name='TESTDB';结果:"TABLE_NAME""COMPOSITE""PARTITION_NAME""SUBPARTITION_COUNT""HIGH_VALUE""HIGH_VALUE_LENGTH""PARTITION_POSITION"
"TESTDB" "NO" "BF110428" 0 "to_date('2011-04-28', 'yyyy-mm-dd')" 35 1
"TESTDB" "NO" "P20110428" 0 "to_date('2011-04-29', 'yyyy-mm-dd')" 35 2
"TESTDB" "NO" "P20110429" 0 "to_date('2011-04-30','YYYY-MM-DD')" 34 3
"TESTDB" "NO" "PMAX" 0 "MAXVALUE" 8 4--拆分分区,把PMAX拆分,增加一个新的的分区。增加4月最后一天的分区报错,请问该如何处理此问题?谢谢!错误如下: alter table TESTDB
split partition PMAX at(to_date('2011-05-01','YYYY-MM-DD'))into (partition P20110430,partition PMAX); 错误报告:
SQL 错误: ORA-14080: 无法按指定的上限来分割分区
14080. 00000 - "partition cannot be split along the specified high bound"
*Cause: User attempted to split a partition along a bound which
either collates higher than that of the partition to be split or
lower than that of a partition immediately preceding the one
to be split
*Action: Ensure that the bound along which a partition is to be split
collates lower than that of the partition to be split and
higher that that of a partition immediately preceding the one
to be split
所以改成日期型吧,要不你下面定义就不要使用to_date转换
create table TESTDB (
MESSAGEID VARCHAR2(38) not null,
STARTTIME DATE not null,
constraint TESTDB_K primary key (MESSAGEID)
)