Oracle中使用ALTER TABLE按范围分区失败问题求助

错误原因解析 #

1. 错误01735：Oracle不支持直接通过ALTER TABLE ... PARTITION BY将已存在的非分区表转换为分区表，该语法仅在创建表时有效，因此触发"invalid ALTER TABLE option"错误。
2. 错误14020：原表是非分区表，无法直接执行ALTER TABLE ADD PARTITION；同时你编写的语法不符合分区表的添加规则，列表分区的分区定义需要正确的语法格式，且仅能针对已分区表操作。

由于category_是取值有限的离散枚举值，**列表分区（LIST Partitioning）**是最适配的方案，比范围分区更直观高效。

方案一：重建分区表（适合小数据量场景） #

1. 创建带分区的新表 #

复制原表结构并添加列表分区定义：

CREATE TABLE unicode_part (
  codepoint nvarchar2(6) PRIMARY KEY,
  charname  nvarchar2(100) NOT NULL,
  category_ nchar(2) NOT NULL,
  combining number NOT NULL,
  bidi nvarchar2(3) NOT NULL,
  decomposition nvarchar2(100),
  decimal_ number,
  digit number,
  numeric_ nvarchar2(100),
  mirrored nchar(1) NOT NULL,
  oldname nvarchar2(100),
  comment_ nvarchar2(100),
  uppercase nvarchar2(6), 
  lowercase nvarchar2(6),
  titlecase nvarchar2(6),
  CONSTRAINT fk_upper_part FOREIGN KEY (uppercase) REFERENCES unicode_part(codepoint),
  CONSTRAINT fk_lower_part FOREIGN KEY (lowercase) REFERENCES unicode_part(codepoint),
  CONSTRAINT fk_title_part FOREIGN KEY (titlecase) REFERENCES unicode_part(codepoint)
)
PARTITION BY LIST (category_) (
  PARTITION p_cc VALUES ('Cc'),
  PARTITION p_cf VALUES ('Cf'),
  PARTITION p_co VALUES ('Co'),
  PARTITION p_cs VALUES ('Cs'),
  PARTITION p_ll VALUES ('Ll'),
  PARTITION p_lm VALUES ('Lm'),
  PARTITION p_lo VALUES ('Lo'),
  PARTITION p_lt VALUES ('Lt'),
  PARTITION p_lu VALUES ('Lu'),
  PARTITION p_default VALUES (DEFAULT) -- 存放未匹配的category_值
);

2. 迁移数据到分区表 #

INSERT INTO unicode_part SELECT * FROM unicode;
COMMIT;

3. 替换原表 #

先备份原表，再重命名分区表为原表名：

RENAME unicode TO unicode_old;
RENAME unicode_part TO unicode;

4. 验证分区 #

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNICODE';

方案二：在线重定义（适合大数据量、无停机场景） #

如果表数据量较大，无法接受停机时间，可使用OracleDBMS_REDEFINITION包在线转换：

1. 检查表是否支持在线重定义 #

替换YOUR_SCHEMA为你的实际用户名：

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('YOUR_SCHEMA', 'UNICODE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

2. 创建临时分区表 #

结构与方案一中的unicode_part一致。

3. 启动在线重定义 #

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => 'YOUR_SCHEMA',
    orig_table => 'UNICODE',
    int_table => 'UNICODE_PART',
    col_mapping => 'codepoint codepoint, charname charname, category_ category_, combining combining, bidi bidi, decomposition decomposition, decimal_ decimal_, digit digit, numeric_ numeric_, mirrored mirrored, oldname oldname, comment_ comment_, uppercase uppercase, lowercase lowercase, titlecase titlecase'
  );
END;
/

4. 复制依赖对象 #

DECLARE
  error_count PLS_INTEGER := 0;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => 'YOUR_SCHEMA',
    orig_table => 'UNICODE',
    int_table => 'UNICODE_PART',
    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers => TRUE,
    copy_constraints => TRUE,
    copy_privileges => TRUE,
    ignore_errors => TRUE,
    num_errors => error_count
  );
  DBMS_OUTPUT.PUT_LINE('Errors: ' || error_count);
END;
/

5. 同步增量数据 #

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('YOUR_SCHEMA', 'UNICODE', 'UNICODE_PART');
END;
/

6. 完成重定义 #

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('YOUR_SCHEMA', 'UNICODE', 'UNICODE_PART');
END;
/

7. 清理临时表 #

验证无误后删除临时表：

DROP TABLE unicode_part;

后续维护 #

若后续新增category_取值，只需执行：

ALTER TABLE unicode ADD PARTITION p_new VALUES ('XX');

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