oracle19c数据库表在线重定义

在线重定义的步骤

以下是使用DBMS_REDEFINITION包进行表在线重定义的基本流程：

1. 准备阶段

• 检查表是否可重定义：确保表符合在线重定义的要求（如表必须是堆表，不能是索引组织表，且不能有正在运行的并行操作等）。

  BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('schema_name', 'table_name');
  END;
  /
  

• 创建临时表（Interim Table）：定义新表结构，作为重定义的目标表。

  CREATE TABLE interim_table (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
  );
  

2. 启动重定义

• 开始重定义过程：

  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE(
      uname => 'schema_name',
      orig_table => 'original_table',
      int_table => 'interim_table'
    );
  END;
  /
  

3. 同步数据

• 同步临时表与原表：确保临时表与原表的数据一致。

  BEGIN
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
      uname => 'schema_name',
      orig_table => 'original_table',
      int_table => 'interim_table'
    );
  END;
  /
  

4. 完成重定义

• 完成重定义并切换表：

  BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE(
      uname => 'schema_name',
      orig_table => 'original_table',
      int_table => 'interim_table'
    );
  END;
  /
  

5. 清理

• 删除临时表（可选）：

  DROP TABLE interim_table;
  

关键注意事项

1. 权限要求：

   • 需要EXECUTE权限在DBMS_REDEFINITION包。
   • 需要对原表和临时表的SELECT、INSERT、UPDATE、DELETE权限。
   • 如果涉及索引或约束，可能需要ALTER权限。

2. 资源消耗：

   • 在同步和重定义过程中，会产生额外的资源消耗（如临时表空间、UNDO和REDO日志）。
   • 确保有足够的磁盘空间和内存。

3. 约束和索引：

   • 需要手动在临时表上创建与原表相同的约束（如主键、外键、唯一约束）和索引。
   • 在FINISH_REDEF_TABLE时，这些约束和索引会自动转移到原表。

4. 限制：

   • 不能修改表的存储参数（如PCTFREE、PCTUSED）。
   • 不支持修改索引组织表（IOT）。
   • 如果表有外键依赖，需先处理主表或从表的约束。

典型应用场景

1. 添加新列：

   -- 创建临时表（包含新列）
   CREATE TABLE interim_table AS SELECT * FROM original_table;
   ALTER TABLE interim_table ADD (new_column VARCHAR2(50));
   
   -- 启动重定义
   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(...);
     ...
   END;
   

2. 修改列数据类型：

   -- 创建临时表（调整列类型）
   CREATE TABLE interim_table (
     column1 VARCHAR2(100),  -- 原类型为VARCHAR2(50)
     column2 NUMBER(10),
     ...
   );
   

3. 重命名表：

   -- 创建临时表（新表名）
   CREATE TABLE new_table AS SELECT * FROM original_table;
   
   -- 启动重定义
   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(...);
     ...
   END;
   

常见问题与解决方案

1. 同步失败：

   • 检查临时表与原表的结构是否一致（列名、数据类型、约束等）。
   • 确保临时表已创建所有必要的索引和约束。

2. 权限不足：

   • 授予用户DBMS_REDEFINITION的EXECUTE权限：

     GRANT EXECUTE ON DBMS_REDEFINITION TO username;
     

3. 空间不足：

   • 确保临时表空间和UNDO表空间足够大。

替代方案

• ALTER TABLE：适用于简单的结构修改（如添加非空列允许NULL），但会锁定表。
• Export/Import：适用于离线环境，但需要完全停止服务。