雅安论坛

标题: oracle海量数据存储组合分区范围分区和列表分区 [打印本页]
作者: 匿名    时间: 2021-12-1 12:12
标题: 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;

  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;



  10. select *
  11. from user_tab_partitions t
  12. where t.table_name = 'CXSJ_DNOLD_4_304';

  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. )

  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. )  

  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.   )





复制代码






欢迎光临 雅安论坛 (https://www.yaanbbs.net/) Powered by Discuz! X3.4