Oracle数据库通过在线重定义的方法新增字段详解.docVIP

Oracle数据库操作中，假如在原始表TB_HXL_USER上新增字段remark01，默认值为A，但是由于该表的数据量比较大，直接在原表上新增字段，执行的时间特别长，最后还报出了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;? 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, ? ? i