oracle分区及子分区

最新推荐文章于 2021-04-08 19:07:51 发布

liu_shui8

最新推荐文章于 2021-04-08 19:07:51 发布

阅读量830

点赞数

文章标签： Oracle SQL

本文链接：https://blog.csdn.net/liu_shui8/article/details/83637148

版权


  --当月天数
  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