当前位置：首页 > 数据库 > oracle > 详细页面

Oracle使用range分区并根据时间列自动创建分区

时间：2023-10-31来源：系统城装机大师作者：佚名

Oracle使用range分区，根据时间列自动创建分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52 -- 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) ;

测试效果

附录：oracle 根据日期自动生成分区表

oracle 根据日期自动生成分区表

1
2
3
4
5
6
7
8
9 CREATE TABLE my_table (
id NUMBER,
name VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION p1 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD'))
);