间隔分区添加分区

准备测试环境：

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