Oracle 19c如何取消分区并保留数据？含引用分区场景

操作步骤 #

1. 创建非分区的主表副本 #

先通过CREATE TABLE ... AS SELECT生成原主表的非分区副本，再补全主键、索引等约束：

-- 创建包含所有数据的非分区主表
CREATE TABLE new_main_table AS SELECT * FROM original_main_table;

-- 添加主键约束（替换为原表实际主键字段）
ALTER TABLE new_main_table ADD CONSTRAINT pk_new_main PRIMARY KEY (main_id);

-- 复制原表的其他索引、约束（根据实际结构调整）
CREATE INDEX idx_new_main_col ON new_main_table(target_column);

2. 重构引用分区子表 #

由于引用分区子表无法直接删除外键或重定义，需通过备份-删除-重建的方式处理：

• 备份子表数据（可选CTAS临时表或数据泵导出）
• 删除原引用分区子表
• 创建非分区子表并建立普通外键关联新主表

示例代码：

-- 备份子表数据到临时表
CREATE TABLE temp_child_table AS SELECT * FROM original_child_table;

-- 删除原引用分区子表（确认备份完成后执行）
DROP TABLE original_child_table PURGE;

-- 创建非分区子表并添加普通外键
CREATE TABLE new_child_table (
    child_id NUMBER,
    main_id NUMBER,
    -- 其他字段
    CONSTRAINT fk_child_main FOREIGN KEY (main_id) REFERENCES new_main_table(main_id)
);

-- 导入备份数据
INSERT INTO new_child_table SELECT * FROM temp_child_table;
COMMIT;

-- 清理临时表（可选）
DROP TABLE temp_child_table PURGE;

重复上述步骤处理所有关联的引用分区子表。

3. 业务切换与收尾 #

将应用访问指向新表，确保业务正常后可处理原表：

-- 重命名原表做备份（可选）
ALTER TABLE original_main_table RENAME TO original_main_table_old;
ALTER TABLE original_child_table RENAME TO original_child_table_old;

-- 将新表重命名为原表名，实现无缝切换
ALTER TABLE new_main_table RENAME TO original_main_table;
ALTER TABLE new_child_table RENAME TO original_child_table;

注意事项 #

• 操作前必须完成原主表及所有子表的全量备份，防止数据丢失。
• 选择业务低峰期执行，降低对业务的影响。
• 复制索引、约束时需与原表结构完全一致，避免破坏数据完整性或性能。
• 超大表场景下，使用EXPDP/IMPDP数据泵进行导出导入会比CTAS更高效，且能保留更多元数据。

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