oracle分区及子分区

tnjexrlliv

--当月天数
select (to_date(to_char(add_months(sysdate, 1), 'yyyymm'), 'yyyymm') -
to_date(to_char(sysdate, 'yyyymm'), 'yyyymm'))
into p_days
from dual;



--创建带子分区的表
create table hwkr.TR_SC_048_MMS_RPT_BADS_D
(
STATIS_MONTH  INTEGER,
STATIS_DATE   DATE,
AREA_CODE     VARCHAR2(2),
AREA_DESC     VARCHAR2(40),
DISTRICT_CODE VARCHAR2(4),
DISTRICT_DESC VARCHAR2(60),
TOTAL_FEE     NUMBER,
CODE          VARCHAR2(3),
RPT_DESC      VARCHAR2(40),
disable_flag  varchar2(4) default '1',
sort_flag     integer default 1
)
tablespace TBS_KR_LIST_M partition by range (STATIS_MONTH)
SUBPARTITION BY list (STATIS_DATE)
(
PARTITION MMS_RPT_BADS_200712 VALUES less than (200801) tablespace TBS_KR_LIST_M
(
subpartition mms_rtp_bads_20071212 values (to_date('2007-12-12','yyyy-mm-dd')) tablespace TBS_KR_LIST_M
)
);


--添加分区
alter table hwkr.tr_sc_048_mms_rpt_bads_d
add partition MMS_RPT_BADS_201007 values less than (201008)
(
subpartition MMS_RPT_BADS_20100601 values (to_date('2010-06-01','yyyy-mm-dd')),
subpartition MMS_RPT_BADS_20100602 values (to_date('2010-06-02','yyyy-mm-dd'))
);



--只添加子分区
alter table hwkr.tr_sc_048_mms_rpt_bads_d
modify partition MMS_RPT_BADS_201006 add
subpartition MMS_RPT_BADS_20100603
values (to_date('2010-06-03','yyyy-mm-dd'));



--删除分区
alter table hwkr.tr_sc_048_mms_rpt_bads_d  
drop partition MMS_RPT_BADS_201006;
--子分区
alter table hwkr.tr_sc_048_mms_rpt_bads_d  
drop subpartition MMS_RPT_BADS_20100601;
--truncate