间隔分区添加分区

间隔分区添加分区 准备测试环境： CREATE TABLE sales (     sale_id NUMBER GENERATED BY DEFAULT AS IDENTITY,     product_id NUMBER,     amount NUMBER,     sale_date DATE ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))  -- 每个分区覆盖1个月 (     PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) ); -- 插入跨越多个分区的数据 INSERT INTO sales (product_id, amount, sale_date) VALUES (1, 150, TO_DATE('2023-01-15', 'YYYY-MM-DD')); INSERT INTO sales (product_id, amount, sale_date) VALUES (2, 200, TO_DATE('2023-02-20', 'YYYY-MM-DD')); INSERT INTO sales (product_id, amount, sale_date) VALUES (3, 180, TO_DATE('2023-03-05', 'YYYY-MM-DD')); INSERT INTO sales (product_id, amount, sale_date) VALUES (4, 220, TO_DATE('2023-04-10', 'YYYY-MM-DD')); INSERT INTO sales (product_id, amount, sale_date) VALUES (5, 250, TO_DATE('2023-05-15', 'YYYY-MM-DD')); -- 提交事务 COMMIT; SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES'; 由于是间隔分区，下面的语句是会报错的。 ALTER TABLE SALES ADD PARTITION p1 VALUES LESS THAN(TO_DATE('2018-09-01','YYYY-MM-DD')); 关闭 间隔 分区 ALTER TABLE sales SET INTERVAL (); 再次添加 SQL> ALTER TABLE SALES ADD PARTITION p1 VALUES LESS THAN(TO_DATE('2018-09-01','YYYY-MM-DD')); ALTER TABLE SALES ADD PARTITION p1 VALUES LESS THAN(TO_DATE('2018-09-01','YYYY-MM-DD'))                                 * ERROR at line 1: ORA-14074: partition bound must collate higher than that of the last partition 使用分区分裂 SPLIT PARTITION ALTER TABLE SALES SPLIT PARTITION P_INITIAL AT (TO_DATE('2020-12-01','YYYY-MM-DD')) INTO (PARTITION P60, PARTITION P_INITIAL) UPDATE GLOBAL INDEXES ; ALTER TABLE SALES SPLIT PARTITION P60 AT (TO_DATE('2020-11-01','YYYY-MM-DD')) INTO (PARTITION P61,PARTITION P60) UPDATE GLOBAL INDEXES ; ALTER TABLE SALES SPLIT PARTITION P61 AT (TO_DATE('2020-10-01','YYYY-MM-DD')) INTO (PARTITION P62,PARTITION P61) UPDATE GLOBAL INDEXES ; 再改回间隔分区：  ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));  select dbms_metadata.get_ddl('TABLE','SALES') from dual; 表定义 SQL> set long 99999 SQL> set pagesize 0 SQL> set linesize 1000 SQL>  select dbms_metadata.get_ddl('TABLE','SALES') from dual;   CREATE TABLE "ZC"."SALES"    ( "SALE_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 99999 99999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  N OKEEP  NOSCALE NOT NULL ENABLE, "PRODUCT_ID" NUMBER, "AMOUNT" NUMBER, "SALE_DATE" DATE    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255   STORAGE(   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"   PARTITION BY RANGE ("SALE_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))  (PARTITION "P61"  VALUES LESS THAN (TO_DATE(' 2020-11-01 00:00:00', 'SYYYY-MM-D D HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "P60"  VALUES LESS THAN (TO_DATE(' 2020-12-01 00:00:00', 'SYYYY-MM-DD  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "P_INITIAL" VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY -MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "SYS_P361"  VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY- MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "SYS_P362"  VALUES LESS THAN (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY- MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "SYS_P363"  VALUES LESS THAN (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY- MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "SYS_P364"  VALUES LESS THAN (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY- MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" ,  PARTITION "SYS_P365"  VALUES LESS THAN (TO_DATE(' 2023-06-01 00:00:00', 'SYYYY- MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" )