在线重定义表是从 oracle9i 开始提供的一个功能，能在线完成对一个表结构或存储的重定义，并且不影响当前应用的使用，是 oracle 高可用性中的一个很有用的特性。
对某个表做在线重定义操作的过程并不影响对原表的查询和 DML 操作，只是在结束在线重定义操作的时候会对原表有短暂的锁定。
在线重定义功能实际是通过物化视图来实现的。

功能概述

  • 修改表或集群的存储参数
  • 移动表或集群到不同的表空间
  • 增加、修改、DROP 表或集群的一个或多个字段
  • 改变分区结构
  • 改变单一表分区的物理属性，包括一定它到相同用户的不同表空间下
  • 改变物化视图日志的物理属性或者 Oracle Streams 高级队列队列表
  • 添加并行查询支持
  • 重建表或者集群减少碎片，在很多情况下，在线 shrink 是减少碎片更容易的方法
  • 将普通表转换为 IOT，或者将 IOT 转换为普通表
  • 转换一个相关表到表的对象字段，或者反向转换
  • 转换一个对象表到相关的表或者表的对象字段，或者反向转换

相关角色与权限

  • EXECUTE_CATALOG_ROLE 角色
  • CREATE ANY TABLE
  • ALTER ANY TABLE
  • DROP ANY TABLE
  • LOCK ANY TALBE
  • SELECT ANY TABLE
  • CREATE ANY TRIGGER
  • CREATE ANY INDEX

操作步骤

选择重定义的方法

  • by key 选择主键或者所有字段有 NOT NULL 约束的唯一键用于在线重定义操作
    使用这种方法，在线重定义之前和之后表应该有相同的主键字段，这是在线重定义操作默认和优先选择的方法。

  • by rowid 如果表没有主键则可以使用这种方法，使用这种方法，隐藏字段 M_ROW$$ 会被添加到重定义后的表中； 推荐在重定义表之后将这个字段 DROP 掉，或者将其标记为 UNUSED。如果 COMPATIBLE 初始化参数设置为 10.2.0 或者更高，最终在线重定义的语法将自动设置这个字段为 UNUSED； 可以使用 ALTER TABLE ... DROP UNUSED COLUMNS 语句 DROP掉它； 不能在索引组织表上使用该方法。

验证表是否能被在线重定义

通过调用 CAN_REDEF_TABLE 过程验证表是否能被在线重定义。如果表不能作为在线重定义表的候选表，那么这个过程会提示一个错误，并且会表明为什么该表不能在线重定义。

SQL> exec dbms_redefinition.can_redef_table(uname => 'SCOTT', tname => 'EMP');

创建一个空的中间表

按照期望的逻辑和物理属性在在线重定义的表相同的用户下创建一个空的中间表，如果要 DROP 字段，那么在中间表中不要包含它们，如果是新增加字段，那么在中间表中添加这些字段，如果是修改字段，那么在中间表按照想要的结果修改它们；
创建中间表不是必须按照重定义的表创建所有的索引、约束、授权和触发器，因为这些对象可以在下面的拷贝依赖对象的步骤中可以被自动定义。

启用行迁移功能

如果采用 rowid 的方法重定义一个分区表，需启用中间表的行迁移功能

ALTER TABLE table_name ENABLE ROW MOVEMENT;

启用会话并行功能

这是可选的步骤，如果重定义一张大表，想通过并行方式提升下面步骤的性能，需执行以下的语句：

ALTER SESSION FORCE PARALLEL DML PARALLEL <degree-of-parallelism>;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL <degree-of-parallelism>;

开始在线重定义过程

提供以下的信息调用 START_REDEF_TABLE 过程开始重定义过程：

  • 被在线重定义的用户名和表名
  • 中间表名称
  • 字段映射字符串，映射被重定义表的字段到中间表的字段
  • 重定义方法
DBMS_REDEFINITION.CONS_USE_PK 代表 by key 方法，这也是默认的方法
DBMS_REDEFINITION.CONS_USE_ROWID 代表 by rowid 方法
  • 在排序行中使用的字段
  • 如果重定义的分区表只有一个分区，注意分区名

因为这步会拷贝数据，故可能会持续一段时间，表在被重新定义的整个过程中，查询和 DML 操作仍然是可执行的； 如果 START_REDEF_TABLE 因为某种原因失败，必须调用 ABORT_REDEF_TABLE 过程，否则接下来重新定义表将失败。

拷贝依赖的对象

从被重定义的表中拷贝依赖的对象（例如：触发器、索引，物化视图日志、授权和约束）和统计信息到中间表，使用以下两种方法的其中一种，方法一是首选的方法，因为它是自动的，该方法也会拷贝统计信息到中间表。

  • 自动创建依赖对象 使用 COPY_TABLE_DEPENDENTS 过程自动创建中间表的依赖对象，这个过程也叫做注册依赖对象。
    在重定义过程完成后，依赖对象的名字将和原有名字保持一致
  • 手动创建依赖对象 可以手动创建中间表的依赖对象，并且注册它们

完成在线重定义过程

执行 FINISH_REDEF_TABLE 过程完成重定义表过程。在这个过程中，原始表将以排他模式被锁定非常短的时间，时间长短依赖于原始表中独立的（与中间表的数据差）数据量，然而，FINISH_REDEF_TABLE 在完成重定义 之前将等待所有的 DML 操作提交。

处理 M_ROS$$ 字段

如果使用 rowid 在线重定义，并且 COMPATIBLE 初始化参数设置为 10.1.0 或者更低，DROP 字段或者设置重定义后的表中出现的 M_ROW$$ 隐藏字段为 UNUSED；

ALTER TABLE table_name set UNUSED(M_ROW$$);

如果 COMPATIBLE 初始化参数为 10.2.0 或者更高，当重定义完成该隐藏字段将自动被设置为 UNUSED，可以使用如下语句 DROP 该字段：

ALTER TABLE table_name DROP UNUSED COLUMNS;

DROP 中间表

等待任何针对中间表长时间运行的查询完成，然后 DROP 中间表。
如果对活动的中间表查询执行 DROP 操作，可能收到 ORA-08103 的错误（"object no longer exists")

使用案例

将 SCOTT 用户下的 EMP 表，通过在线重定义的方法，将其修改为分区表

  • 验证源表在线重定义的条件，使用主键(默认)方法进行在线重定义验证
SQL> exec dbms_redefinition.can_redef_table(uname => 'SCOTT', tname => 'EMP');
  • 创建中间表
create table emp_range_tab (
  empno     number(4,0), 
  ename     varchar2(10), 
  job       varchar2(10), 
  mgr       number(4,0), 
  hiredate  date, 
  sal       number(7,2), 
  comm      number(7,2),   
  deptno    number(2,0) 
)
partition by range(hiredate)(
  partition p1 values less than (date'1980-01-01'), 
  partition p2 values less than (date'1981-01-01'), 
  partition pmax values less than (maxvalue) 
);
  • 开始重定义过程
SQL> exec dbms_redefinition.start_redef_table(uname => 'SCOTT', orig_table => 'EMP', int_table => 'EMP_RANGE_TAB');
  • 拷贝依赖对象（自动在 EMP_RANGE_TAB 表上创建触发器，索引，约束等）
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  dbms_redefinition.copy_table_dependents(uname => 'SCOTT', orig_table => 'EMP', int_table => 'EMP_RANGE_TAB', ignore_errors => true, num_errors => num_errors);
END;

在这儿 ignore_errors 参数设置为 TRUE，根据实际情况，部分错误可直接忽略
此时可检查索引、约束是否拷贝成功

  • 查询 dba_redefinition_error 视图检查错误
SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;
  • 同步中间表
SQL> exec dbms_redefinition.sync_interim_table(uname => 'SCOTT', orig_table => 'EMP', int_table => 'EMP_RANGE_TAB');
  • 完成重定义（源表和中间表切换属性）
SQL> exec dbms_redefinition.finish_redef_table(uname => 'SCOTT', orig_table => 'EMP', int_table => 'EMP_RANGE_TAB');

EMP 表将被以排他模式锁定很短的时间，直到这步结束，之后 EMP 被重定义完成，EMP 将有 EMP_RANGE_TAB 表的所有属性

  • 检查源表是否分区成功
SELECT '1980' PART, count(*) CNT FROM emp partition(p1)
UNION ALL
SELECT '1981' PART, COUNT(*) CNT FROM emp PARTITION(p2)
UNION ALL
SELECT 'MAXVALUE' PART, count(*) CNT FROM emp PARTITION(pmax);

PART            CNT
-------- ----------
1980              0
1981              1
MAXVALUE         13


SQL> select t.table_name, t.partition_name from user_tab_partitions t where t.table_name = 'EMP';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
EMP                            P1
EMP                            P2
EMP                            PMAX
  • 删除中间表
SQL> DROP TABLE EMP_RANGE_TAB PURGE;

原创文章，转载请注明出处：http://www.opcoder.cn/article/14/