oracle海量数据存储组合分区范围分区和列表分区

1. //创建表空间
   
2. 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;
   
3. 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;
   
4. 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;
   
5. 
   
6. 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;
   
7. 
   
8. 
   
9. 
   
10. select *
    
11. from user_tab_partitions t
    
12. where t.table_name = 'CXSJ_DNOLD_4_304';
    
13. 
    
14. //创建范围类型的分区表
    
15. CREATE TABLE CXSJ_DNOLD_4_304
    
16. (
    
17.     ID      NUMBER NOT NULL PRIMARY KEY,
    
18.     ENDTIME    DATE,
    
19.     OID VARCHAR2(200),
    
20.     LOU VARCHAR2(50),
    
21.                 DIZHI VARCHAR2(100),
    
22.                 JG VARCHAR2(30),
    
23.                 FJ VARCHAR2(30),
    
24.                 ZHI VARCHAR2(50),
    
25.                 LU VARCHAR2(10)
    
26.                
    
27. )
    
28. 
    
29. PARTITION BY RANGE(ENDTIME) SUBPARTITION BY LIST (LU)  
    
30. (  
    
31.          PARTITION DNOLD_4_304_1 VALUES LESS THAN (TO_DATE('2021-10-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_304_1       
    
32. (   
    
33.       SUBPARTITION OLD_4_304_1_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_1,   
    
34.       SUBPARTITION OLD_4_304_1_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_1   
    
35.   ),   
    
36.   PARTITION DNOLD_4_304_2 VALUES LESS THAN (TO_DATE('2021-11-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_304_2  
    
37.   (   
    
38.       SUBPARTITION DNOLD_4_304_2_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_2,   
    
39.       SUBPARTITION DNOLD_4_304_2_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_2   
    
40.   ),
    
41.         PARTITION DNOLD_4_304_3 VALUES LESS THAN (TO_DATE('2021-12-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_304_3  
    
42.   (   
    
43.       SUBPARTITION DNOLD_4_304_3_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_3,   
    
44.       SUBPARTITION DNOLD_4_304_3_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_3   
    
45.   )  
    
46.        
    
47.          
    
48. )  
    
49. 
    
50. //添加新分区(包含子分区)
    
51. 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
    
52. (   
    
53.       SUBPARTITION DNOLD_4_304_4_1 VALUES ('A') TABLESPACE CXSJ_DNOLD_4_304_4,   
    
54.       SUBPARTITION DNOLD_4_304_4_2 VALUES ('B') TABLESPACE CXSJ_DNOLD_4_304_4   
    
55.   )
    
56. 
    
57. 
    
58. 
    
59. 
    
60. 
    

复制代码