oracle增加欄位帶預設值

在oracle 11gR2版本中，對大表增加帶預設值的欄位，需要拆分成多個步驟，否則會長時間鎖表。如下圖：

對260萬資料的表加帶預設值的欄位，執行超過2分鐘。

我們的規範做法步驟為：

（1）加欄位

alter table  T_ORDER add tstatus varchar2(20);

（2）批量更新資料

declare

  n_count number;

begin

  select ceil(count(1)/100000) into n_count

  from T_ORDER where tstatus is null;

  for i in 1..n_count loop

    update T_ORDER set tstatus='1' where tstatus is null and rownum<=100000;

    commit;

  end loop;

end;

/

（3）增加預設值屬性

alter table TABLE_NAME modify tstatus default '1' not null;

在19c中不再需要如此繁冗的操作了，新增帶預設值的欄位可以瞬間完成：

實驗準備：	create table test( owner varchar2(30), object_name varchar2(128), object_type varchar2(30), created date ); insert into test  select owner,object_name,object_type ,to_Date('20190101','yyyymmdd')+60*dbms_random.value from all_objects; commit; --重複執行insert操作，插入200萬資料 insert into test select * from test; commit; SQL> select count(1) from test;   COUNT(1) ----------    3461376
oracle11gR2版本：
	會話1	會話2
結論1：在oracle11gR2版本中，進行新增列、修改列的預設值操作時，如果其他會話中沒有未提交的ddl、dml操作，則可以瞬間完成。	SQL> set timing on SQL> alter table test add col2 varchar2(10) ; Table altered. Elapsed: 00:00:00.00 SQL>  SQL>  SQL> alter table test modify col2 default '1'; Table altered. Elapsed: 00:00:00.00 SQL>  SQL> select count(1) from test where col2='1';   COUNT(1) ----------          0 Elapsed: 00:00:00.04
結論2：在oracle11gR2中，直接新增帶預設值的列，執行時間和表的資料量相關	SQL> alter table test add col3 varchar2(10) default '1'; Table altered. Elapsed: 00:01:49.02 SQL> SQL> SQL>  SQL> alter table test add col4 date default sysdate; Table altered. Elapsed: 00:02:04.62
結論3：當有DML操作未提交時，新增帶預設值的列將報錯(獲取獨佔鎖失敗）。新增列不帶預設值時，會等待dml操作提交（釋放行級鎖）後才可執行完成。		SQL> set time on 15:17:50 SQL> delete from test where rownum=1; 1 row deleted.
	SQL> set time on 15:18:11 SQL> alter table test add col5 varchar2(10) default '1'; alter table test add col5 varchar2(10) default '1'             * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:00:00.00 15:18:16 SQL>  alter table test add col5 varchar2(10);
		15:17:54 SQL> commit; Commit complete. 15:18:43 SQL>
	Table altered. Elapsed: 00:00:20.35 15:18:43 >
結論4：新增預設值帶預設值、非空約束時，如果沒有DML阻塞，可以瞬間完成；如果有DML操作未提交，則需等待直到DML操作提交才可完成	15:24:50 SQL> alter table test add col6 varchar2(10) default '1' not null; Table altered. Elapsed: 00:00:00.01
		15:27:55 SQL>  delete from test where rownum=1; 1 row deleted.
	15:28:01 SQL> alter table test add col7 varchar2(10) default '1' not null;
		15:28:47 SQL> commit; Commit complete. 15:29:04 SQL>
	Table altered. Elapsed: 00:00:09.27 15:29:04 SQL>
oracle19c版本	實驗準備操作相同，資料量基本一致 SQL> SQL>  select count(1) from test;   COUNT(1) ----------    3479400
	會話1	會話2
結論5：在19c版本中，增加帶預設值、無非空約束的列，可以瞬間完成。如果有DML操作未結束，仍需等待該操作完成才可以結束。	SQL> set timing on SQL> alter table test add col3 varchar2(10) default '1'; Table altered. Elapsed: 00:00:00.01 SQL>  SQL> alter table test add col4 date default sysdate; Table altered. Elapsed: 00:00:00.02
		SQL> set time on 15:43:01 SQL> delete from test where rownum=1; 1 row deleted.
	15:43:07 SQL> alter table test add col5 varchar2(10) default '1';
		15:43:03 SQL> commit; Commit complete. 15:43:24 SQL>
	Table altered. Elapsed: 00:00:05.76 15:43:24 SQL> 15:43:24 SQL>

在19c官方文件中有如下描述：

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-and-Manual-Locking-Mechanisms-During-SQL-Operations.html#GUID-1B08DE66-5ED8-4BEF-893B-B887E3A82D50

直譯為

11.2版本中 alter table add column with default value的操作正常不會阻塞，但在使用supplemental log時會降級為阻塞操作。

實際測試該操作，將資料庫開啟最小輔助日誌、表開啟輔助日誌（ alter table testu.test2 add supplemental log data(all) columns;），然後對test表使用OGG同步。操作都可以瞬間完成。這裡還沒太明白，後續有進展再補充。

總結：

在11gR2版本中增加帶預設值的列時，需要指定not null屬性，即可瞬間完成；否則鎖表時間較長。

在19c版本中 增加帶預設值的列時，不管是否包含not null屬性，都可瞬間完成。

操作需要在業務低峰期操作，避免操作時有dml操作對其造成阻塞。

感悟：

資料庫中的DML操作，主要需要避免大事務造成的鎖表時間長、佔用redo及undo等資源巨大、資源不足時回滾操作不可控等問題；

DDL操作時除了操作時間，還需要考慮表的獨佔鎖對其他操作的阻塞問題。

隨著資料庫版本的迭代，功能愈加完善，很多經驗已經不再適用了。適用新版本資料庫前，應該對規範、操作手冊進行測試，與時俱進，提高效率。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-2698825/，如需轉載，請註明出處，否則將追究法律責任。