char字段类型怎么to_date分区？

显示全部楼层 · 2012-10-9 18:08:15

create table operation.T_CSRC_TEST
( day CHAR(10) not null,
investornameCHAR(10)
)partition by range (day)(
partition p_2012_03 values less than (to_date('2012-03-31','yyyy-mm-dd')) tablespace tbs_history_2012_03,
partition p_2012_06 values less than (to_date('2012-06-30','yyyy-mm-dd')) tablespace tbs_history_2012_06,
partition p_2012_09 values less than (to_date('2012-09-30','yyyy-mm-dd')) tablespace tbs_history_2012_09,
partition p_max values less than (maxvalue) tablespace tbs_historysettlement);
公司内的系统，放了好多年的数据，现在不堪重负，我想利用day字段来划分分区表
日期的保存格式全是以2014-09-01的格式化保存，按照上面SQL建立分区表分执行报错
提示：ORA-14037: 分区 "P_2012_03" 的分区界限过高

请问有什么好的办法解决吗？
字段类型是定死的，不能修改，不然程序必定出错

千问 · 2012-10-9 18:08:15

改成这样啊。
create table operation.T_CSRC_TEST
( day CHAR(10) not null,
investornameCHAR(10)
)partition by range (day)(
partition p_2012_03 values less than (to_date('2012-04-01','yyyy-mm-dd')) tablespace tbs_history_2012_03,
partition p_2012_06 values less than (to_date('2012-06-30','yyyy-mm-dd')) tablespace tbs_history_2012_06,
partition p_2012_09 values less than (to_date('2012-10-01','yyyy-mm-dd')) tablespace tbs_history_2012_09,
partition p_max values less than (maxvalue) tablespace tbs_historysettlement);

千问 · 2012-10-9 18:08:15

如果你的DAY字段的内容是严格按照YYYY-MM-DD的形式填写的话，可以试试如下的建表语句：
create table operation.T_CSRC_TEST
( day CHAR(10) not null,
investornameCHAR(10)
)partition by range (day)(
partition p_2012_03 values less than ('2012-03-31') tablespace tbs_history_2012_03,
partition p_2012_06 values less than ('2012-06-30') tablespace tbs_history_2012_06,
partition p_2012_09 values less than ('2012-09-30') tablespace tbs_history_2012_09,
partition p_max values less than (maxvalue) tablespace tbs_historysettlement);

另外，“less than"是小于的意思。”less than ('2012-03-31')“表示小于'2012-03-31'的值才会存放到该分区中，也就是说，‘2012-03-31’这个值不会被存放到该分区。如果希望'2012-03-31'存放到该分区，条件要改为less than ('2012-04-01').