在线重定义大数据量表(新增字段)

目的：在原始表TB_HXL_USER上新增字段remark01,默认值为'A',该表的数据量大概有4个亿,试着用普通的做法直接在原表上新增字段,执行了很长时间，最后报undo空间不足而失败,而且在新增字段的过程中，其他用户还不能访问该表,出现的等待事件是library cache lock.下面试着通过在线重定义的方法新增字段，能够避免undo空间不足以及其他用户不能访问该表的情况.

 

1.使用如下SQL获取原始表的DDL

--设置分隔符号以及去掉表DDL中的storage属性

begin
    Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform,
                                    'SQLTERMINATOR',
                                    True);
  Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform,
                                    'STORAGE',
                                    False);
end;

－－提取表,索引,约束,以及权限的语句,若索引和约束、权限没有的话，可以将相应的语句去掉

Select Dbms_Metadata.Get_Ddl(Object_Type => 'TABLE', Name => 'TB_HXL_USER') ||
       Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'INDEX',
                                       Base_Object_Name => 'TB_HXL_USER') ||
       Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'CONSTRAINT',
                                       Base_Object_Name => 'TB_HXL_USER') ||
       Dbms_Metadata.Get_Dependent_Ddl('OBJECT_GRANT', 'TB_HXL_USER', 'HXL')
  From Dual

2.将步骤1 SQL中的表名 TB_HXL_USER 替换为 TB_HXL_USER_MID 创建中间表

 

3.中间表新增字段 remark01

alter table TB_HXL_USER_MID add remark01 varchar2(10) default 'A';

4.检查能否进行重定义,过程执行成功即说明可以重定义，表必须有主键或唯一性约束才能重定义

Begin
  Dbms_Redefinition.Can_Redef_Table(USER, 'TB_HXL_USER');
 End;

若表没有主键的话，可以通过rowid进行检查

Begin
 Dbms_Redefinition.Can_Redef_Table(USER, 'TB_TEST01',DBMS_REDEFINITION.cons_use_rowid );
End

5.开始重定义表

注意:如原始表有未提交的事物,该过程会一直在等待,等待事件为enq: TX - row lock contention

--不能执行start_redef_table的情况下,需要将如下权限赋予用户

grant create any table to hxl;
grant alter any table to hxl;
grant drop any table to hxl;
grant lock any table to hxl;
grant select any table to hxl;
grant create any trigger to hxl;
grant create any index to hxl;

--运行start_redef_table过程

BEGIN
  dbms_redefinition.start_redef_table(
    uname => USER,
    orig_table => 'TB_HXL_USER',
    int_table => 'TB_HXL_USER_MID',
    options_flag => DBMS_REDEFINITION.cons_use_pk);
    -- 如果有主键则是 options_flag => DBMS_REDEFINITION.cons_use_pk，如果没有DBMS_REDEFINITION.cons_use_rowid

END;

6.开始同步中间表

BEGIN
  dbms_redefinition.sync_interim_table(
    uname => USER,
    orig_table => 'TB_HXL_USER',
    int_table => 'TB_HXL_USER_MID');
END;

7.完成同步
注意:如原始表有未提交的事物,该过程会一直在等待

BEGIN
  dbms_redefinition.finish_redef_table(
    uname => USER,
    orig_table => 'TB_HXL_USER',
    int_table => 'TB_HXL_USER_MID');
END;

8.删除中间表

drop table tb_hxl_user_mid;

9.修改索引名称

alter index idx_tb_hxl_user_mid_n1 rename to idx_tb_hxl_user_n1;
alter index idx_tb_hxl_user_mid_u1 rename to idx_tb_hxl_user_u1;