oracle数据库,分区键要非空并设置默认值,根据分区键类型不同分别为DEFAULT systimestamp not null和DEFAULT sysdate not null。
接入区分区表示例
1.TIMESTAMP类型,按小时
create table test{
cl1,
time, TIMESTAMP(6) DEFAULT systimestamp not null
cl2}tablespace xxxx
PARTITION BY RANGE (time)
interval (NUMTODSINTERVAL(1,'HOUR'))
(
partition p_first values less than (TIMESTAMP '2019-03-14 16:00:00')
)
2.TIMESTAMP类型,按天
create table test{
cl1,
time, TIMESTAMP(6) DEFAULT systimestamp not null
cl2}tablespace xxxx
PARTITION BY RANGE (time)
interval (NUMTODSINTERVAL(1,'DAY'))
(
partition p_first values less than (TIMESTAMP '2019-03-14 00:00:00')
)
3.DATE类型,按小时
create table test{
cl1,
time, DATE DEFAULT sysdate not null
}tablespace xxxx
PARTITION BY RANGE (time)
interval (NUMTODSINTERVAL(1,'HOUR'))
(
partition p_first values less than (TO_DATE('2019-03-14 16:00:00','YYYY-MM-DD HH24:MI:SS'))
)
4.DATE类型,按天
create table test{
cl1,
time, DATE DEFAULT sysdate not null
}tablespace xxxx
PARTITION BY RANGE (time)
interval (NUMTODSINTERVAL(1,'DAY'))
(
partition p_first values less than (TO_DATE('2019-03-14 00:00:00','YYYY-MM-DD HH24:MI:SS'))
)