createTABLE TABLE1
(
TABLE_ID NUMBER(8),
SUB_DATE DATE,
VALUENUMBER(8)
)
PARTITIONBYRANGE(SUB_DATE)
INTERVAL(NUMTOYMINTERVAL(1，'MONTH'))
(
PARTITION P1 VALUESLESSTHAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
2014年5月1日前的数据会放入p1分区，5月1日后的数据每月只要有数据，就会自动创建一个分区。
INTERVAL (numtodsinterval(1,'DAY'))—按天分区
------------------------------------打卡记录表分区
createtable TB_GJ_CARDRECORD
(
c_oid_cardrecord NUMBER(19) notnull,
c_empid NUMBERnotnull,
c_kqdatetime DATE,
c_kqtime VARCHAR2(64),
c_datatypeid NUMBER,
c_oprtid NUMBER,
c_iskoukuan VARCHAR2(64),
c_remark VARCHAR2(512),
c_resultmark VARCHAR2(512),
c_ismodified VARCHAR2(64),
c_devid NUMBER,
c_hardwarerecordid NUMBER,
c_recid NUMBER
)
partitionbyrange (C_KQDATETIME)
INTERVAL(NUMTOYMINTERVAL(1，'MONTH'))
(
PARTITION P1 VALUESLESSTHAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
tablespace IC_MIDEA
pctfree10
initrans1
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
)
);
-- Add comments to the table
commentontable TB_GJ_CARDRECORD
is'打卡明细表';
-- Add comments to the columns
commentoncolumn TB_GJ_CARDRECORD.c_oid_cardrecord
is'记录号
';
commentoncolumn TB_GJ_CARDRECORD.c_empid
is'人员ID
';
commentoncolumn TB_GJ_CARDRECORD.c_kqdatetime
is'考勤日期
';
commentoncolumn TB_GJ_CARDRECORD.c_kqtime
is'考勤时间 ';
commentoncolumn TB_GJ_CARDRECORD.c_datatypeid
is'数据类型1：正常刷卡(缺省)2：手工签卡
';
commentoncolumn TB_GJ_CARDRECORD.c_oprtid
is'操作员
';
commentoncolumn TB_GJ_CARDRECORD.c_iskoukuan
is'是否扣款
';
commentoncolumn TB_GJ_CARDRECORD.c_remark
is'备注
';
commentoncolumn TB_GJ_CARDRECORD.c_resultmark
is'结果标记
';
commentoncolumn TB_GJ_CARDRECORD.c_ismodified
is'是否修改
';
commentoncolumn TB_GJ_CARDRECORD.c_devid
is'设备ID
';
commentoncolumn TB_GJ_CARDRECORD.c_hardwarerecordid
is'卡机记录ID
';
commentoncolumn TB_GJ_CARDRECORD.c_recid
is'原记录ID
';
-- Create/Recreate primary, unique and foreign key constraints
altertable TB_GJ_CARDRECORD
addconstraint PK_TB_GJ_CARDRECORD_OID primarykey (C_OID_CARDRECORD)
usingindex
tablespace IC_MIDEA
pctfree10
initrans2
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
);
-- Create/Recreate indexes
createindex PK_C_EMPID on TB_GJ_CARDRECORD (C_EMPID)
tablespace IC_MIDEA
pctfree10
initrans2
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
);
createindex PK_C_KQDATETIME on TB_GJ_CARDRECORD (C_KQDATETIME)
tablespace IC_MIDEA
pctfree10
initrans2
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
);
createindex PK_C_KQTIME on TB_GJ_CARDRECORD (C_KQTIME)
tablespace IC_MIDEA
pctfree10
initrans2
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
);
createindex PK_EMPID_KQDATETIME on TB_GJ_CARDRECORD (C_EMPID, C_KQDATETIME)
tablespace IC_MIDEA
pctfree10
initrans2
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
);
删除分区表中的分区
alter table tablename drop partition partname;
alter table tb_gj_cardrecord drop partition SYS_P81;
