Oracle數(shù)據庫操作中，假如在原始表TB_HXL_USER上新增字段remark01，默認值為'A'，但是由于該表的數(shù)據量比較大，直接在原表上新增字段，執(zhí)行的時間特別長，最后還報出了undo空間不足的問題。而且在新增字段的過程中，其他用戶還不能訪問該表,出現(xiàn)的等待事件是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 創(chuàng)建中間表

3.中間表新增字段 remark01

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

4.檢查能否進行重定義,過程執(zhí)行成功即說明可以重定義

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

5.開始重定義表

注意:如原始表有未提交的事物,該過程會一直在等待,等待事件為enq: TX - row lock contention。

不能執(zhí)行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; 

執(zhí)行完以上的9個步驟，新增字段就創(chuàng)建成功了。

關于Oracle數(shù)據庫用在線重定義的方法新增字段的操作就介紹到這里了，希望本次的介紹能夠對您有所收獲！

【編輯推薦】

1. 關于Oracle數(shù)據庫閃回個性的詳細介紹
2. Oracle數(shù)據庫對DDL語句和DML語句的事務管理
3. Oracle數(shù)據庫啟動參數(shù)文件及相關SQL語句簡介
4. Oracle數(shù)據庫的幾種文件及表空間數(shù)據塊的知識簡介
5. Oracle數(shù)據庫查詢登錄用戶名所屬表空間及其使用情況