1、建立测试表
– 创建RANGE分区表
CREATE TABLE T_RANGE_12 (
ID INT,
NAME VARCHAR(50),
CREATE_TIME DATE
) PARTITION BY RANGE(ID) (
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN (50),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
) STORAGE(ON TSG_12);

– 插入测试数据
BEGIN
FOR i IN 1…60 LOOP
INSERT INTO T_RANGE_12 VALUES (i, ‘range_’ || i, SYSDATE);
END LOOP;
COMMIT;
END;
/
2、测试步骤
2.1 合并前检查
– 查看分区定义
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = ‘T_RANGE_12’;

– 查看各分区数据量（可逐个查询）
SELECT COUNT() FROM T_RANGE_12 PARTITION (p1);
SELECT COUNT() FROM T_RANGE_12 PARTITION (p2);
SELECT COUNT() FROM T_RANGE_12 PARTITION (p3);
SELECT COUNT() FROM T_RANGE_12 PARTITION (p4);

– 查看各BP数据分布
CALL SP_GET_EP_COUNT(‘USER_12’, ‘T_RANGE_12’);

2.2 执行分区合并
ALTER TABLE T_RANGE_12 MERGE PARTITIONS p1, p2 INTO PARTITION p1_2;
2.3 合并后验证
SELECT COUNT(*) FROM T_RANGE_12 PARTITION (p1_2); – 应包含ID 1-29的数据

– 查看分区定义
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = ‘T_RANGE_12’;