oracle创建月份分区表,oracle创建月分区表

-- Create table

create table T_PARTITION_MONTH

(

FID         VARCHAR2(32) not null,

MONTH       VARCHAR2(2) default to_char(sysdate,'MM'),

CREATE_DATE DATE default sysdate

)

partition by list (MONTH)

(

partition P01 values ('01')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P02 values ('02')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P03 values ('03')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P04 values ('04')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P05 values ('05')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P06 values ('06')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P07 values ('07')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P08 values ('08')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P09 values ('09')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P10 values ('10')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P11 values ('11')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition P12 values ('12')

tablespace TBS_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

)

)

;

-- Add comments to the table

comment on table T_PARTITION_MONTH

is '测试01';

-- Add comments to the columns

comment on column T_PARTITION_MONTH.FID

is '主键';

comment on column T_PARTITION_MONTH.MONTH

is '月分区字段';

comment on column T_PARTITION_MONTH.CREATE_DATE

is '创建时间';

-- Create/Recreate primary, unique and foreign key constraints

alter table T_PARTITION_MONTH

add constraint PK_PARTITION_MONTH primary key (FID)

using index

tablespace TBS_DATA

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);