CREATE TABLE GW20_PAYORDER
(
BANKTRANSDATE VARCHAR2(20 BYTE),
BANKRESULT VARCHAR2(10 BYTE),
PAYRESULT VARCHAR2(2 BYTE) NOT NULL,
PAYRESULTDESC VARCHAR2(50 BYTE),
PAYTYPE VARCHAR2(20 BYTE),
SERVICETYPE NUMBER(4) NOT NULL,
SERVICEXML VARCHAR2(200 BYTE),
CURRENCY VARCHAR2(10 BYTE) NOT NULL,
ORDERPRICE NUMBER(12,4) NOT NULL,
CREATEDATE DATE NOT NULL,
PAYDATE DATE NOT NULL,
STATUS VARCHAR2(1 BYTE) NOT NULL,
OPERNAME VARCHAR2(20 BYTE),
SYNMERCHANTFLG NUMBER(2) NOT NULL,
JMSBILLSTATE VARCHAR2(1 BYTE) DEFAULT '0',
VERSIONNUM NUMBER(3) NOT NULL,
SERVICEDESC VARCHAR2(100 BYTE),
PROVIDERADDR VARCHAR2(30 BYTE)
)
PARTITION BY RANGE(CREATEDATE)
(
PARTITION P2015M03 VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M04 VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M05 VALUES LESS THAN (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M06 VALUES LESS THAN (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M07 VALUES LESS THAN (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M08 VALUES LESS THAN (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M09 VALUES LESS THAN (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M10 VALUES LESS THAN (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M11 VALUES LESS THAN (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P2015M12 VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
这个是按照月的
主要是修改 分区的名字 P2015M03 和 日期，
PARTITION BY RANGE(CREATEDATE) 就是你分区的字段
CREATE INDEX GW20_PAYORDER_JMS ON GW20_PAYORDER
(JMSBILLSTATE)
local ;
创建ｉｎｄｅｘ 的时候，后面多个 local
//查询分区表
select TABLE_NAME,PARTITION_NAME from user_tab_partitions order by TABLE_NAME,PARTITION_NAME;
//增加新分区
alter table CHANNEL_CHARGE_RECORD_SPECIAL add PARTITION P2018M01 values less than(TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;