Oracle动态创建时间分区，以及Oracle12c中快速创建自增列

1.时间单分区

时间分区可以按照年月日时分秒进行分区，一般按照日或月分区就足够了，这里按照的是日分区

demo如下

create table APDEMO
(
OID NUMBER(38) generated as identity (start with 1 increment by 1),--主键，自增列
REPORTTIME DATE , 
AP_MAC VARCHAR2(64 CHAR) , 
APALIASNAME VARCHAR2(128 CHAR) not null, 
HOTSPOTNAME VARCHAR2(128 CHAR) not null, 
LONGITUDE VARCHAR2(64 CHAR) , 
CITY VARCHAR2(50 CHAR) not null,
constraint pk_APDEMO_OID primary key (OID)
)
partition by range(REPORTTIME) interval (numtodsinterval(1,'day'))
(
partition part_20170101 values less than (to_date('20170101','yyyyMMdd'))   --这里需要加入一个起始日期
);

2.　时间地市组合分区

 

create table ZJ2_BUDSTA
(
P_DAY               DATE          ,                         
CITY                VARCHAR2(100) ,                         
BUILDINGID          VARCHAR2(50)  ,                         
BUILDINGNAME        VARCHAR2(500) 
)
partition by range(p_day) interval(NUMTODSINTERVAL(1,'day'))
subpartition by list(CITY)
subpartition template
(
subpartition zj_570 values('570'),
subpartition zj_571 values('571'),
subpartition zj_572 values('572'),
subpartition zj_573 values('573'),
subpartition zj_574 values('574'),
subpartition zj_575 values('575'),
subpartition zj_576 values('576'),
subpartition zj_577 values('577'),
subpartition zj_578 values('578'),
subpartition zj_579 values('579'),
subpartition zj_580 values('580')
)
(
partition zj_date_20160101 values less than (to_date('2016-01-01','yyyy-MM-dd'))
);

　　

 

　分区貌似不能在已有表上创建，只能新建表后将原表数据导入进来，而且初始日期一定要小于数据最小的日期，否则会导致多个日期的数据在同一个分区中