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

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;

提取表，索引，约束以及权限的语句。

SelectDbms_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') FromDual

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

3.中间表新增字段 remark01

altertableTB_HXL_USER_MIDaddremark01varchar2(10)default'A';

4.检查能否进行重定义,过程执行成功即说明可以重定义

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

5.开始重定义表

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

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

grantcreateanytabletohxl; grantalteranytabletohxl; grantdropanytabletohxl; grantlockanytabletohxl; grantselectanytabletohxl; grantcreateanytriggertohxl; grantcreateanyindextohxl;

运行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.删除中间表

droptabletb_hxl_user_mid;

9.修改索引名称

alterindexidx_tb_hxl_user_mid_n1renametoidx_tb_hxl_user_n1; alterindexidx_tb_hxl_user_mid_u1renametoidx_tb_hxl_user_u1;

执行完以上的9个步骤，新增字段就创建成功了。

关于Oracle数据库用在线重定义的方法新增字段的操作就介绍到这里了，希望本次的介绍能够对您有所收获！

【编辑推荐】

关于Oracle数据库闪回个性的详细介绍Oracle数据库对DDL语句和DML语句的事务管理Oracle数据库启动参数文件及相关SQL语句简介Oracle数据库的几种文件及表空间数据块的知识简介Oracle数据库查询登录用户名所属表空间及其使用情况