- //创建表空间
- 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
- )
-
-
-
-
-
-
复制代码
|