oracle分区表新增主分区及子分区语句

时间：2010-12-22 来源：hero--008

源表：

CREATE TABLE dw_account_logout_info
(
    logout_time DATE NOT NULL ,
    gateway_id INTEGER NOT NULL ,
    online_time NUMBER NOT NULL ,
    game_id INTEGER NOT NULL ,
    account VARCHAR2(32) NOT NULL ,
    role VARCHAR2(20) NULL
)
PARTITION BY RANGE (logout_time)
SUBPARTITION BY LIST (game_id)
(
PARTITION par_12_2009 VALUES LESS THAN(TO_DATE('2010-01-01','yyyy-mm-dd'))
(SUBPARTITION logout_122009_jx2wz VALUES (7) TABLESPACE logout_122009_jx2wz,
SUBPARTITION logout_122009_fs3 VALUES (11) TABLESPACE logout_122009_fs3),
PARTITION par_01_2010 VALUES LESS THAN(TO_DATE('2010-02-01','yyyy-mm-dd'))
(SUBPARTITION logout_012010_jx2wz VALUES (7) TABLESPACE logout_012010_jx2wz,
SUBPARTITION logout_012010_fs3 VALUES (11) TABLESPACE logout_012010_fs3))；
新增主分区及子分区：
alter table DW_ACCOUNT_LOGOUT_INFO
  add partition PAR_06_2011 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DW_TBS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
  (
    subpartition LOGOUT_052011_JX3 values (2) tablespace LOGOUT_062011_JX3
  );
单独新增子分区：
alter table DW_ACCOUNT_LOGOUT_INFO
  modify partition PAR_12_2009
    add subpartition LOGOUT_122009_JX3 values (2) tablespace logout_122009_jx3;
alter table DW_ACCOUNT_LOGOUT_INFO
  modify partition PAR_01_2010
    add subpartition LOGOUT_012010_JX3 values (2) tablespace logout_012010_jx3;