oracle 修改子分区,oracle子分区

可以通过 subpartition template 管理子分区。

一般在表建立后，直接alter table xxx add partition xxxx values ....即可，子分区会自动根据模板创建。

修改子分区模板：

alter table xxx set subpartition template xxxxxxx;

create table MOD_CDL_CFC

(

START_TIME    DATE,

ADM_AREA      NUMBER(10),

NE_SYS_ID     NUMBER(16),

CHINA_NAME    VARCHAR2(200),

RELATED_BTS   NUMBER(16),

RELATED_BSC   NUMBER(16),

RELATED_OMC   NUMBER(16),

CARRIER       NUMBER,

CFC           NUMBER,

FIRSTCFC      NUMBER,

SECONDCFC     NUMBER,

SERVICEOPTION NUMBER,

CFCCOUNT      NUMBER,

CITY_ID       NUMBER,

VENDOR        VARCHAR2(10)

)

tablespace noap_data

partition by range (START_TIME)

subpartition by list ("CITY_ID")

subpartition template (

subpartition c27  values(27 ),

subpartition c754 values(754),

subpartition c755 values(755),

subpartition c20  values(20 ),

subpartition c769 values(769),

subpartition c660 values(660),

subpartition c756 values(756),

subpartition c763 values(763),

subpartition c757 values(757),

subpartition c760 values(760))

(

partition MOD_CDL_CFC_2009090100 values less than (TO_DATE(' 2009-09-1 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace noap_data

( subpartition MOD_CDL_CFC_2009090100_c27  values(27 ) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c754 values(754) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c755 values(755) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c20  values(20 ) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c769 values(769) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c660 values(660) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c756 values(756) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c763 values(763) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c757 values(757) tablespace noap_data,

subpartition MOD_CDL_CFC_2009090100_c760 values(760) tablespace noap_data)

);