Oracle修改非空字段类型

1. 创建与原表结构相同的中间表

CREATE TABLE TEST_TMP 
TABLESPACE TSP_TEST
PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 64K NEXT 8K MINEXTENTS 1 MAXEXTENTS UNLIMITED)
AS SELECT * FROM TEST WHERE 1=0;

2. 修改目标字段精度

ALTER TABLE TEST_TMP MODIFY (RED_BLOOD NUMBER(6,1));

3. 添加主键约束（在线重定义必须有主键/ROWID）

ALTER TABLE TEST_TMP 
ADD CONSTRAINT PK_TEST_TMP 
PRIMARY KEY (PID, VID)
USING INDEX TABLESPACE TSP_TEST
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED);

4. 在中间表上创建索引（提前创建可避免FINISH时重建索引的长时间锁）

CREATE INDEX IDX_IN_DATE_TIME_TMP ON TEST_TMP (IN_DATE_TIME)
TABLESPACE TSP_TEST PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED);
CREATE INDEX IND_TEST_1_TMP ON TEST_TMP (START_DATE_TIME)
TABLESPACE TSP_TEST PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED);

数据迁移

BEGIN
  -- 1. 校验是否可重定义
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'TEST_USER',
    tname        => 'TEST',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );

  -- 2. 开始重定义（全量数据拷贝，此步骤耗时最长但不锁表）

  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        =>  'TEST_USER',
    orig_table   => 'TEST',
    int_table    => 'TEST_TMP',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/
BEGIN

  -- 3. 同步增量变更（可多次执行以减少最终切换窗口）
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => 'MEDSURGERY',
    orig_table => 'TEST',
    int_table  => 'TEST_TMP'
  );

  -- 4. 完成切换（仅短暂TM锁，通常<1秒）
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'MEDSURGERY',
    orig_table => 'TEST',
    int_table  => 'TEST_TMP'
  );
END;
/

7. 验证字段已修改成功

SELECT DATA_TYPE, DATA_PRECISION, DATA_SCALE 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'TEST' AND COLUMN_NAME = 'RED_BLOOD';

8. 重新授予权限（FINISH后原表对象不变，但建议确认）可以提前将授权语句复制出来，修改后直接执行