Oracle分区表数据未正确插入对应分区（生产环境求助）

问题背景 #

存在一张按EFF_DT字段做范围分区的表RSA_DBA.RSA_ADHOC_ACCT，插入数据时部分数据能正确进入对应分区，其余数据分配异常。

建表语句 #

CREATE TABLE RSA_DBA.RSA_ADHOC_ACCT
(   
    RSA_ACCT_DTL_GID NUMBER(12,0) NOT NULL, 
    EFF_DT DATE NOT NULL, 
    RSA_CMPSN_REC_GID NUMBER(12,0), 
    ACCT_ID NUMBER(12,0), 
    ACCT_CMPSN_AMT NUMBER(16,3), 
    INSRT_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, 
    INSRT_TS DATE DEFAULT SYSDATE NOT NULL, 
    UPDT_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, 
    UPDT_TS DATE DEFAULT SYSDATE NOT NULL, 
    PRCS_RUN_ID NUMBER(12,0), 
    SRC_TS DATE, 
    VLD_IND CHAR(1 BYTE), 

    CONSTRAINT PK_RSA_ADHOC_ACCT_DTL_AI 
        PRIMARY KEY (RSA_ACCT_DTL_GID, EFF_DT)
)
PARTITION BY RANGE (EFF_DT) 
(
    PARTITION PTN_M20221201  VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    PARTITION PTN_M20230101  VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    PARTITION PTN_M20230201  VALUES LESS THAN (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    -- 中间省略其他分区定义
    PARTITION PTN_M20241001  VALUES LESS THAN (TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    PARTITION PTN_M20241101  VALUES LESS THAN (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

注：原建表语句末尾存在语法缺失，已修正补全

数据查询情况 #

执行以下SQL统计每日数据量：

select trunc(EFF_DT), count(ACCT_CMPSN_AMT) 
from RSA_DBA.RSA_ADHOC_ACCT
group by  trunc(EFF_DT)
order by 1;

结果显示多个日期存在有效数据，但对应分区数据量与统计结果不匹配。

分区数据验证 #

执行以下SQL查询分区数据量：

SELECT table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name = 'RSA_ADHOC_ACCT' and NUM_ROWS > 0
ORDER BY table_name, partition_name;

结果仅2个分区数据量符合预期，其余分区数据量异常。

排查修复步骤 #

1. 确认分区定义正确性 #

检查所有分区的边界值是否连续、无重叠或缺口：

SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'RSA_ADHOC_ACCT'
ORDER BY partition_position;

将high_value转换为实际日期值，确认每个分区的边界是前一个分区的起始日期（例如PTN_M20230101的边界为2023-02-01，对应PTN_M20221201的范围是2022-12-01至2023-01-01）。

2. 验证数据实际所在分区 #

针对异常日期的数据，查询其实际存储的分区：

SELECT 
    trunc(t.EFF_DT) AS eff_date,
    p.partition_name,
    count(*) AS row_count
FROM RSA_DBA.RSA_ADHOC_ACCT t
JOIN dba_tab_partitions p 
    ON t.table_name = p.table_name
    AND DBMS_ROWID.ROWID_OBJECT(t.rowid) = p.object_id
WHERE trunc(t.EFF_DT) IN ('异常日期1', '异常日期2') -- 替换为实际异常日期
GROUP BY trunc(t.EFF_DT), p.partition_name;

确认数据是否被错误分配到其他分区（如最大边界分区或错误区间的分区）。

3. 检查分区表状态 #

• 确认所有分区状态正常：

SELECT partition_name, status
FROM dba_tab_partitions
WHERE table_name = 'RSA_ADHOC_ACCT';

确保所有分区状态为USABLE。

4. 修复数据分配异常 #

方式1：分区交换（适合大量数据）

1. 创建与原表结构一致的非分区临时表
2. 将错误分区的数据导出到临时表
3. 清空错误分区
4. 重新插入临时表数据到原表（会自动分配到正确分区）
5. 清理临时表

方式2：移动数据（适合少量数据）

ALTER TABLE RSA_DBA.RSA_ADHOC_ACCT MOVE PARTITION 错误分区名;

该命令会重新整理分区内数据，将不符合分区规则的数据移动到正确分区，生产环境建议低峰期操作，注意锁表影响。

方式3：重新插入数据

若数据量不大，可导出异常数据，删除原表中异常数据后重新插入，确保插入时EFF_DT值符合分区规则。

5. 预防措施 #

• 使用INTERVAL PARTITIONING自动分区，避免手动创建分区时出现边界错误
• 插入数据前校验EFF_DT值是否在预期分区范围内
• 定期检查分区数据量与实际数据日期的匹配情况

内容的提问来源于stack exchange，提问作者Pooja