Oracle使用range分区并根据时间列自动创建分区（oracle 按时间排序）一篇读懂

– Create table
create table MY_TEST
(
id NUMBER (12) not null,
name VARCHAR2 (12) not null,
today TIMESTAMP (6) default SYSDATE
)
partition by range (today) interval(numtodsinterval(1,’day’)) –月分区用month，年分区用year
(
partition P_20230411 values less than (TO_DATE(‘2023-04-12 00:00:00,”SYYYY-MM-DD HH24:MI:SS”))
tablespace TB_SAMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
)
);
– – Add comments to the table
comment on table MY_TEST
is ‘测试表’;
Add comments to the columns
comment on column MY_TEST.id
is ‘主键id’;
comment on column MY_TEST.name
is ‘名称’;
comment on column MY_TEST.today
is ‘时间’;

– – Create/Recreate indexes
create index MY_TEST_INDEX on MY_TEST (id)
tablespace TB_SAMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
– -Grant/Revoke object privileges
grant select on MY_TEST to DBVIEW;

INSERT INTO MY_TEST VALUES (1,’xxc1′,SYSDATE);
INSERT INTO MY_TEST VALUES (2,’xxc2′?SYSDATE+1);
INSERT INTO MY_TEST VAIUES (3,’xxc3′,SYSDATE+2) ;