oracle 定义子分区,测试oracle子分区维护

今天忙中偷闲，简单测试了一下一种子分区维护，以后抽空都测一下，测试结果如下：

1、创建子分区测试表

CREATE TABLE mylong

(YYYYMMDD DATE,

TYPE NUMBER,

cnt NUMBER

)

PARTITION BY RANGE(yyyymmdd)

SUBPARTITION BY list(TYPE)

SUBPARTITION TEMPLATE

(SUBPARTITION "type1" VALUES(1),

SUBPARTITION "type2" VALUES(2)

)

(PARTITION p20080701 VALUES less than (TO_DATE('20080702','yyyymmdd')),

PARTITION p20080702 VALUES less than (TO_DATE('20080703','yyyymmdd')),

PARTITION p20080703 VALUES less than (TO_DATE('20080704','yyyymmdd'))

);

查看分区情况：

OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SUMBLOCK

-------- ------------------ -------------------------------- -------------------- ------------------------- ----------

MCC TABLE SUBPARTITION MYLONG P20080701_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080701_type2 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080702_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080702_type2 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080703_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080703_type2 TBS_MCC_DATA 1

----------

SUMALL 6

2、增加分区：

ALTER TABLE mylong ADD PARTITION p20080704 VALUES less than (TO_DATE('20080705','yyyymmdd'));

查看分区情况

OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SUMBLOCK

-------- ------------------ -------------------------------- -------------------- ------------------------- ----------

MCC TABLE SUBPARTITION MYLONG P20080701_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080701_type2 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080702_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080702_type2 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080703_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080703_type2 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080704_type1 TBS_MCC_DATA 1

MCC TABLE SUBPARTITION MYLONG P20080704_type2 TBS_MCC_DATA 1

----------

SUMALL 8[@more@]