使用 DBMS_REDEFINITION 在线重定义表（完整流程）

命令

-- 1) Is it eligible? (use CONS_USE_ROWID if there is no PK)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CUSTOMER_APP', 'CUSTOMER', DBMS_REDEFINITION.CONS_USE_PK);

-- 2) Interim table with the target structure: hash partitioned in this example
CREATE TABLE customer_app.customer_int (
  id NUMBER, name VARCHAR2(100), city VARCHAR2(30))
PARTITION BY HASH (id) PARTITIONS 8;

-- 3) Start: initial copy + change tracking through an MV log
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'CUSTOMER_APP',
    orig_table   => 'CUSTOMER',
    int_table    => 'CUSTOMER_INT',
    col_mapping  => NULL,
    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- 4) Copy indexes, constraints, triggers, grants and statistics:
SET SERVEROUTPUT ON
DECLARE
  l_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'CUSTOMER_APP',
    orig_table       => 'CUSTOMER',
    int_table        => 'CUSTOMER_INT',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => TRUE);
  DBMS_OUTPUT.PUT_LINE('Error count: ' || l_errors);
END;
/
SELECT object_name, base_table_name, ddl_txt FROM dba_redefinition_errors;

-- 5) Close the gap before finishing (shortens finish; can be run several times):
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('CUSTOMER_APP', 'CUSTOMER', 'CUSTOMER_INT');

-- 6) Swap under a short lock:
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('CUSTOMER_APP', 'CUSTOMER', 'CUSTOMER_INT', dml_lock_timeout => 60);

-- 7) Drop the interim table, which now holds the old structure:
DROP TABLE customer_app.customer_int PURGE;

-- On problems: EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('CUSTOMER_APP', 'CUSTOMER', 'CUSTOMER_INT');
-- Status: SELECT * FROM dba_redefinition_status WHERE base_table_owner = 'CUSTOMER_APP';

作用

在应用持续运行的同时更改表结构（分区、列类型/顺序、表空间、压缩、LOB 设置）：数据复制到中间表，变更通过 MV log 同步，最后在极短的锁下交换名称。

适用场景

用于 ALTER TABLE 无法在线完成的变更，例如在不停机的情况下对大型非分区表进行分区、更改列类型，或将 BasicFile LOB 转换为 SecureFiles。

注意

需要 Enterprise Edition（如果目标是分区表，还需要 Partitioning 选件）。该过程需要两倍于表的空间，并增加 redo 和 MV log 开销。FINISH 会短暂获取排他锁；长时间运行的事务会使其等待（dml_lock_timeout）。务必在 DBA_REDEFINITION_ERRORS 中检查 COPY_TABLE_DEPENDENTS 的错误；FK 可能以 NOVALIDATE 方式复制。请先在测试环境中演练。