oracle海量数据存储组合分区范围分区和列表分区
//创建表空间create tablespace CXSJ_DNOLD_4_304_1 datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\CXSJ_DNOLD_4_304_1.dbf' size 100m autoextend on next 50m;
create tablespace CXSJ_DNOLD_4_304_2 datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\CXSJ_DNOLD_4_304_2.dbf' size 100m autoextend on next 50m;
create tablespace CXSJ_DNOLD_4_304_3 datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\CXSJ_DNOLD_4_304_3.dbf' size 100m autoextend on next 50m;
create tablespace CXSJ_DNOLD_4_304_4 datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\CXSJ_DNOLD_4_304_4.dbf' size 100m autoextend on next 50m;
select *
from user_tab_partitions t
where t.table_name = 'CXSJ_DNOLD_4_304';
//创建范围类型的分区表
CREATE TABLE CXSJ_DNOLD_4_304
(
ID NUMBER NOT NULL PRIMARY KEY,
ENDTIME DATE,
OID VARCHAR2(200),
LOU VARCHAR2(50),
DIZHI VARCHAR2(100),
JG VARCHAR2(30),
FJ VARCHAR2(30),
ZHI VARCHAR2(50),
LU VARCHAR2(10)
)
PARTITION BY RANGE(ENDTIME) SUBPARTITION BY LIST (LU)
(
PARTITION DNOLD_4_304_1 VALUES LESS THAN (TO_DATE('2021-10-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_304_1
(
SUBPARTITION OLD_4_304_1_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_1,
SUBPARTITION OLD_4_304_1_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_1
),
PARTITION DNOLD_4_304_2 VALUES LESS THAN (TO_DATE('2021-11-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_304_2
(
SUBPARTITION DNOLD_4_304_2_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_2,
SUBPARTITION DNOLD_4_304_2_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_2
),
PARTITION DNOLD_4_304_3 VALUES LESS THAN (TO_DATE('2021-12-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_304_3
(
SUBPARTITION DNOLD_4_304_3_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_3,
SUBPARTITION DNOLD_4_304_3_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_3
)
)
//添加新分区(包含子分区)
ALTER TABLE CXSJ_DNOLD_4_304 ADD PARTITION DNOLD_4_304_4 VALUES LESS THAN(TO_DATE('2022-01-01','YYYY-MM-DD')) TABLESPACE CXSJ_DNOLD_4_304_4
(
SUBPARTITION DNOLD_4_304_4_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_4,
SUBPARTITION DNOLD_4_304_4_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_4
)
页:
[1]