基于 dbms_redefinition 在线重定义表

Oracle 支持在线重定义表，也就是说我们可以在修改表结构(DDL)的同时进行相关的DQL、DML操作，使得前端的DML根本感觉不到表结构实际上已经发生了变化，对于用户而言是完全透明的。当然在线重定义期间，前端性能会稍微有所下降。Oracle提供的重定义包dbms_redefinition即是用与完成此操作。其实质是Oracle使用了智能物化视图及物化视图日志的方式。在对象结构重组期间，表现为一个本地对象的复制，重组期间发生的任何变化都会被刷新到最新。

1、在线重定义表的主要功能：
   修改表或簇的存储参数
    将表移动到相同或不同schema下不同的tablespace(如果不要求表始终可用的话，也可以直接使用alter table move 实现)
     为表添加，修改或删除列
     为表添加或删除分区，改变分区结构
     改变物化视图日志或者Streams Advanced Queuing queue 表结构
     增加并行查询支持
     重建表以减少碎片
     将堆表变为索引组织表或相反

2、图示在线重定义
下面的图示便于理解是如何进行在线重定义，其本质是基于基表的一个快照
基于 dbms_redefinition 在线重定义表

3、在线重定义的步骤
    a、选择在线重定义的方式，基于键(主键或唯一键)还是rowid(无主键或唯一键的情形)
     b、校验表能否被在线重定义，使用过程CAN_REDEF_TABLE
     c、创建用于在线重定义的临时表(该临时表使用新的表结构，即添加删除列，列长度变化，存储属性变化等)
     d、如果重定义的为分区表且使用rowid方式，应该为临时表开启行移动(ALTER TABLE ... ENABLE ROW MOVEMENT;)
     e、对于大型表的在线重定义可以通过启用并行以提高性能(此步骤可选)
          ALTER SESSION FORCE PARALLEL DML PARALLEL degree-of-parallelism;
          ALTER SESSION FORCE PARALLEL QUERY PARALLEL degree-of-parallelism;
     f、调用过程start_redef_table启动在线重定义，如果启动失败，应调用ABORT_REDEF_TABLE先终止查找原因后再次启动
     g、从被重定义的表复制依赖对象到临时表(triggers, indexes, materialized view logs, grants, and constraints)及统计信息到临时表
     h、同步被重定义的表到临时表(调用过程sync_interim_table，此步可选)
     i、调用过程FINISH_REDEF_TABLE完成表的在线重订义
     j、删除临时表

4、演示在线重定义

--下面基于主键来演示在线重定义

--环境

scott@USBO> select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--创建需要重定义的表

scott@USBO> create table tb_emp(empno number(4) not null,ename varchar2(10),

  2  job varchar2(10),hiredate varchar2(20),sal number(7,2),deptno number(2));

--下面为其添加相关约束

scott@USBO> alter table tb_emp add constraint pk_tb_emp primary key(empno);

scott@USBO> alter table tb_emp add constraint fk_tb_emp_dept_no foreign key(deptno) references dept(deptno);

--创建用于在线重定义的临时表

--注意，empno列可以为NULL，ename变化为name且长度增加，sal变为salary，数据精度发生变化，以及deptno使用了default

scott@USBO> create table tb_emp_int

  2  (empno number(4),name varchar2(20),hiredate varchar2(20),salary number,deptno number(2) default 30);

--基于重定义创建触发器

scott@USBO> CREATE OR REPLACE TRIGGER tr_bf_tb_emp_hiredate

  2     BEFORE UPDATE OF hiredate

  3     ON tb_emp

  4     FOR EACH ROW

  5  BEGIN

  6     :new.hiredate := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');

  7  END tr_bf_tb_emp_hiredate;

  8  /

Trigger created.

--基于中间表创建触发器

scott@USBO> CREATE OR REPLACE TRIGGER tr_bf_tb_emp_int_hiredate

  2     BEFORE UPDATE OF hiredate

  3     ON tb_emp_int

  4     FOR EACH ROW

  5  BEGIN

  6     :new.hiredate := TO_CHAR (SYSDATE + 10, 'yyyymmdd hh24:mi:ss');

  7  END tr_bf_tb_emp_int_hiredate;

  8  /

Trigger created.

--收集统计信息

scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP',cascade=>true);

scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP_INT',cascade=>true);

scott@USBO> select table_name,num_rows from user_tables where table_name like 'TB_EMP%';

TABLE_NAME                       NUM_ROWS

------------------------------ ----------

TB_EMP                                  0

TB_EMP_INT                              0

--对重定义表执行DML操作

--此时使用了dbms_lock.sleep (5)，也就是整个操作完成需要500s，我们在这个期间实施重定义

scott@USBO> get ins_tb_emp.sql

  1  DECLARE

  2     v_deptno   NUMBER (2);

  3  BEGIN

  4     FOR i IN 1 .. 100

  5     LOOP

  6        IF MOD (i, 2) = 0

  7        THEN

  8           v_deptno := 10;

  9        ELSE

 10           v_deptno := 20;

 11        END IF;

 12        INSERT INTO tb_emp

 13           SELECT i,

 14                  'Name_' || TO_CHAR (i),

 15                  'Job_' || TO_CHAR (i),

 16                  TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss'),

 17                  i + 100,

 18                  v_deptno

 19             FROM DUAL;

 20        dbms_lock.sleep (5);

 21        COMMIT;

 22     END LOOP;

 23* END;

 24  /

--下面再开启一个新的session，用于在线重定义表

scott@USBO> set serveroutput on;

--校验表能否被重定义

scott@USBO> exec dbms_redefinition.can_redef_table('SCOTT','TB_EMP');

--开始重定义

scott@USBO> exec dbms_redefinition.start_redef_table('SCOTT', 'TB_EMP', 'TB_EMP_INT',-

> 'EMPNO EMPNO, ENAME NAME, SAL*1.10 SALARY, HIREDATE HIREDATE,DEPTNO DEPTNO');

--可以从视图user_snapshots查询到临时表的信息

scott@USBO> select name,table_name,updatable,status from user_snapshots;

NAME                           TABLE_NAME                     UPD STATUS

------------------------------ ------------------------------ --- -------

TB_EMP_INT                     TB_EMP_INT                     NO  VALID

--查看表tb_emp_int，此时也有5条记录被插入

scott@USBO> select count(*) from tb_emp_int;

  COUNT(*)

----------

         5

--正在插入到tb_emp_int产生的日志信息，从6开始，实际上执行start_redef_table时前5条记录已经被复制到临时表

scott@USBO> select * from mlog$_tb_emp;

     EMPNO SNAPTIME$$        D O CHANGE_VEC      XID$$

---------- ----------------- - - ---------- ----------

         6 40000101 00:00:00 I N FE         1.4074E+15

         7 40000101 00:00:00 I N FE         5.6299E+14

         8 40000101 00:00:00 I N FE         2.2519E+15

         9 40000101 00:00:00 I N FE         1.4075E+15

scott@USBO> select * from rupd$_tb_emp;

no rows selected

--检查约束等是否已经添加到临时表

scott@USBO> select constraint_name,table_name,status from user_constraints where table_name='TB_EMP_INT';

no rows selected

--注册依赖对象

scott@USBO> exec dbms_redefinition.register_dependent_object('SCOTT', 'TB_EMP', 'TB_EMP_INT', -

> dbms_redefinition.cons_trigger, 'SCOTT', 'tr_bf_tb_emp_hiredate', 'tr_bf_tb_emp_int_hiredate');

PL/SQL procedure successfully completed.

scott@USBO> select constraint_name,table_name,status from user_constraints where table_name='TB_EMP_INT';

no rows selected

--将重定义表的依赖对象复制到临时表

scott@USBO> DECLARE

  2     retval   NUMBER (5);

  3  BEGIN

  4     DBMS_REDEFINITION.copy_table_dependents ('SCOTT', 'TB_EMP', 'TB_EMP_INT', 0,

  5                                              copy_constraints   => TRUE,

  6                                              num_errors         => retval);

  7     DBMS_OUTPUT.put_line (retval);

  8  END;

  9  /

PL/SQL procedure successfully completed.

--查看临时表上的依赖对象，可以看到出现了以TMP$$开头的相关约束

scott@USBO> select constraint_name,table_name,status from user_constraints where table_name in('TB_EMP_INT','TB_EMP');

CONSTRAINT_NAME                TABLE_NAME                     STATUS

------------------------------ ------------------------------ --------

SYS_C0024681                   TB_EMP                         ENABLED

PK_TB_EMP                      TB_EMP                         ENABLED

FK_TB_EMP_DEPT_NO              TB_EMP                         ENABLED

TMP$$_SYS_C00246810            TB_EMP_INT                     ENABLED

TMP$$_PK_TB_EMP0               TB_EMP_INT                     ENABLED

TMP$$_FK_TB_EMP_DEPT_NO0       TB_EMP_INT                     DISABLED

--查看表tb_emp，此时重定义表插入了36条记录

scott@USBO> select count(*) from tb_emp;

  COUNT(*)

----------

        36

--临时表上的记录为5

scott@USBO> select count(*) from tb_emp_int;

  COUNT(*)

----------

         5                       

--下面的过程用于同步重定义表与临时表

scott@USBO> exec dbms_redefinition.sync_interim_table('SCOTT', 'TB_EMP', 'TB_EMP_INT');

PL/SQL procedure successfully completed.           

--这是同步后的结果

scott@USBO> select count(*) from tb_emp_int;

  COUNT(*)

----------

        39

--最后完成在线重定义，此时如果重定义表上事务没有被完成，需要等到所有事务完成

scott@USBO> exec dbms_redefinition.finish_redef_table('SCOTT', 'TB_EMP', 'TB_EMP_INT');

PL/SQL procedure successfully completed.

scott@USBO> select count(*) from tb_emp_int;

  COUNT(*)

----------

       100       

--检验结果，可以看到列salary上的值发生了变化

scott@USBO> select new.salary new_sal, old.sal old_sal,new.deptno new_deptno,old.deptno old_detpno

  2  from tb_emp new, tb_emp_int old

  3  where new.empno = old.empno and rownum<=3;

   NEW_SAL    OLD_SAL NEW_DEPTNO OLD_DETPNO

---------- ---------- ---------- ----------

     111.1        101         20         20

     112.2        102         10         10

     113.3        103         20         20

--验证触发器

scott@USBO> select table_name, trigger_name

  2  from user_triggers;

TABLE_NAME                     TRIGGER_NAME

------------------------------ ------------------------------

TB_EMP                         TR_BF_TB_EMP_HIREDATE

TB_EMP_INT                     TR_BF_TB_EMP_INT_HIREDATE

--Author : Leshami

--Blog   : http://blog.csdn.net/leshami

--此时临时表上的触发器被应用到重定义的表

scott@USBO> select trigger_body from user_triggers

  2  where table_name = 'TB_EMP';

TRIGGER_BODY

--------------------------------------------------------------------------------

BEGIN

   :new.hiredate := to_char(SYSDATE + 10,'yyyymmdd hh24:mi:ss');

END tr_bf_int_emp_hiredate;

scott@USBO> desc tb_emp

 Name                                                                                Null?    Type

 ----------------------------------------------------------------------------------- -------- --------------------

 EMPNO                                                                                        NUMBER(4)

 NAME                                                                                         VARCHAR2(20)

 HIREDATE                                                                                     VARCHAR2(20)

 SALARY                                                                                       NUMBER

 DEPTNO                                                                                       NUMBER(2)

 scott@USBO> select column_name,data_type, data_default from dba_tab_columns

  2  where owner='SCOTT' and table_name='TB_EMP' and column_name='DEPTNO';

Column Name          Data Type                 DATA_DEFAULT

-------------------- ------------------------- ------------------------------------------------------------------

DEPTNO               NUMBER                    30