Oracle數(shù)據(jù)庫(kù)操作中，假如在原始表TB_HXL_USER上新增字段remark01，默認(rèn)值為'A'，但是由于該表的數(shù)據(jù)量比較大，直接在原表上新增字段，執(zhí)行的時(shí)間特別長(zhǎng)，最后還報(bào)出了undo空間不足的問(wèn)題。而且在新增字段的過(guò)程中，其他用戶還不能訪問(wèn)該表,出現(xiàn)的等待事件是library cache lock。

下面試著通過(guò)在線重定義的方法新增字段，能夠避免undo空間不足以及其他用戶不能訪問(wèn)該表的情況。

1.使用如下SQL獲取原始表的DDL

設(shè)置分隔符號(hào)以及去掉表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; 

提取表，索引，約束以及權(quán)限的語(yǔ)句。

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.檢查能否進(jìn)行重定義,過(guò)程執(zhí)行成功即說(shuō)明可以重定義

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

5.開(kāi)始重定義表

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

不能執(zhí)行start_redef_table的情況下,需要將如下權(quán)限賦予用戶。

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; 

運(yùn)行start_redef_table過(guò)程

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，如果沒(méi)有

DBMS_REDEFINITION.cons_use_rowid  
END; 

6.開(kāi)始同步中間表

BEGIN  
 
dbms_redefinition.sync_interim_table(  
 
uname => USER,  
 
orig_table => 'TB_HXL_USER',  
 
int_table => 'TB_HXL_USER_MID');  
 
END; 

7.完成同步

注意：如原始表有未提交的事物,該過(guò)程會(huì)一直在等待

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個(gè)步驟，新增字段就創(chuàng)建成功了。

關(guān)于Oracle數(shù)據(jù)庫(kù)用在線重定義的方法新增字段的操作就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

1. 關(guān)于Oracle數(shù)據(jù)庫(kù)閃回個(gè)性的詳細(xì)介紹
2. Oracle數(shù)據(jù)庫(kù)對(duì)DDL語(yǔ)句和DML語(yǔ)句的事務(wù)管理
3. Oracle數(shù)據(jù)庫(kù)啟動(dòng)參數(shù)文件及相關(guān)SQL語(yǔ)句簡(jiǎn)介
4. Oracle數(shù)據(jù)庫(kù)的幾種文件及表空間數(shù)據(jù)塊的知識(shí)簡(jiǎn)介
5. Oracle數(shù)據(jù)庫(kù)查詢登錄用戶名所屬表空間及其使用情況