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中不再需要如此繁冗的操作了，添加带默认值的字段可以瞬间完成：

在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操作时除了操作时间，还需要考虑表的独占锁对其他操作的阻塞问题。

随着数据库版本的迭代，功能愈加完善，很多经验已经不再适用了。适用新版本数据库前，应该对规范、操作手册进行测试，与时俱进，提高效率。