cmds在线重定义增加列

--输出信息采用缩排或换行格式化
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
--确保每个语句都带分号
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
--关闭表索引、外键等关联(后面单独生成)
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', FALSE);
--关闭存储、表空间属性
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'STORAGE', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', FALSE);
--关闭创建表的PCTFREE、NOCOMPRESS等属性
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);

Oracle数据库操作中,假如在原始表TB_HXL_USER上新增字段remark01,默认值为'A',但是由于该表的数据量比较大,直接在原表上新增字段,执行的时间特别长,最后还报出了undo空间不足的问题。而且在新增字段的过程中,其他用户还不能访问该表,出现的等待事件是library cache lock。
下面试着通过在线重定义的方法新增字段,能够避免undo空间不足以及其他用户不能访问该表的情况。

1.使用如下SQL获取原始表的DDL
设置分隔符号以及去掉表DDL中的storage属性

SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => 'TABLE', NAME => 'INSURED') ||  
DBMS_METADATA.GET_DEPENDENT_DDL(OBJECT_TYPE => 'INDEX',BASE_OBJECT_NAME => 'INSURED') ||  
DBMS_METADATA.GET_DEPENDENT_DDL(OBJECT_TYPE => 'CONSTRAINT',BASE_OBJECT_NAME => 'INSURED' ) ||  
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'INSURED', 'CMDS')  
FROM DUAL;

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 => 'INSURED') ||  Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'INDEX',  Base_Object_Name => 'INSURED') From Dual ;

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

3.中间表新增字段 remark01
alter table insured_MID add remark01 varchar2(10) default 'A'; 
4.检查能否进行重定义,过程执行成功即说明可以重定义
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE('cmds','insured',DBMS_REDEFINITION.CONS_USE_rowid);
end;
/


begin
DBMS_REDEFINITION.CAN_REDEF_TABLE('cmds','insured_mid',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过程

SQL> BEGIN  
dbms_redefinition.start_redef_table(uname => USER,orig_table => 'insured', int_table => 'INSURED_MID', options_flag => DBMS_REDEFINITION.cons_use_rowid); 
END; 
/
如果有主键则是options_flag => DBMS_REDEFINITION.cons_use_pk,如果没有DBMS_REDEFINITION.cons_use_rowid  

6.开始同步中间表
BEGIN  
dbms_redefinition.sync_interim_table(uname => 'CMDS',orig_table => 'INSURED',int_table => 'INSURED_MID');  
END;
/


7.完成同步
注意:如原始表有未提交的事物,该过程会一直在等待
BEGIN  
dbms_redefinition.finish_redef_table(uname => 'CMDS',orig_table => 'INSURED',int_table => 'INSURED_MID');  
END;
/


8.删除中间表
drop table insured_mid;
 
9.修改索引名称
alter index IDX_INSURED_NO_mid rename to IDX_INSURED_NO;  
alter index IDX_INSURED_INDEX_mid rename to IDX_INSURED_INDEX

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

posted @ 2017-03-31 23:06  WSmallTail  阅读(146)  评论(0编辑  收藏  举报