要为一个已有分区(如clah_201110)增加子分区,发现只能一个个加,好麻烦呀,如下代码。不知道大家有没有什么好办法。当然对没有分区,可以一起加上分区与子分区。看来分区前来是规划好是重要的。
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p3 values (3);
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p4 values (4);
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p5 values (5);
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p6 values (6);
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p7 values (7);
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p8 values (8);
alter table cl_tah modify partition clah_201110 add subpartition clah_201110_p9 values (9);
当然可以在语句的背后加上表空间的参数。
附原表:create table cl_tah(
ID NUMBER(10) not null,
NODEID NUMBER(10),
LSCID NUMBER(10),
VALUE NUMBER(20,5),
UPDATETIME DATE,
STATIONID NUMBER(10),
COLLTIME DATE
)
partition by range(colltime) subpartition by list(lscid)
(
partition clah_201110 values less than(to_date('2011-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201110_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201110_p2 values (2) tablespace tbs_nh_his
),
partition clah_201111 values less than(to_date('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201111_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201111_p2 values (2) tablespace tbs_nh_his
),
partition clah_201112 values less than(to_date('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201112_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201112_p2 values (2) tablespace tbs_nh_his
),
partition clah_201201 values less than(to_date('2012-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201201_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201201_p2 values (2) tablespace tbs_nh_his
),
partition clah_201202 values less than(to_date('2012-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201202_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201202_p2 values (2) tablespace tbs_nh_his
),
partition clah_201203 values less than(to_date('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201203_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201203_p2 values (2) tablespace tbs_nh_his
),
partition clah_201204 values less than(to_date('2012-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201204_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201204_p2 values (2) tablespace tbs_nh_his
),
partition clah_201205 values less than(to_date('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201205_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201205_p2 values (2) tablespace tbs_nh_his
),
partition clah_201206 values less than(to_date('2012-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201206_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201206_p2 values (2) tablespace tbs_nh_his
),
partition clah_201207 values less than(to_date('2012-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201207_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201207_p2 values (2) tablespace tbs_nh_his
),
partition clah_201208 values less than(to_date('2012-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201208_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201208_p2 values (2) tablespace tbs_nh_his
),
partition clah_201209 values less than(to_date('2012-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201209_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201209_p2 values (2) tablespace tbs_nh_his
),
partition clah_201210 values less than(to_date('2012-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201210_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201210_p2 values (2) tablespace tbs_nh_his
),
partition clah_201211 values less than(to_date('2012-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201211_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201211_p2 values (2) tablespace tbs_nh_his
),
partition clah_201212 values less than(to_date('2012-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
(
subpartition clah_201212_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201212_p2 values (2) tablespace tbs_nh_his
),
partition clah_201301 values less than(maxvalue)
(
subpartition clah_201301_p1 values (1) tablespace tbs_nh_his,
subpartition clah_201301_p2 values (2) tablespace tbs_nh_his
)
)