Oracle數據庫通過在線重定義的方法新增字段詳解
作者：hxl
本文我們主要介紹了Oracle數據庫操作中，當數據庫表的數據量比較大的時候，采用在線重定義的方法新增字段的方法，以避免undo空間不足以及其他用戶不能訪問該表的情況發生。希望能夠對您有所幫助。
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',
- 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;
執行完以上的9個步驟，新增字段就創建成功了。
關于Oracle數據庫用在線重定義的方法新增字段的操作就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
責任編輯：趙鵬
來源：
ChinaUnix博客
