四川盐亭网站制作公司

oracle增加字段带默认值

在oracle 11gR2版本中，对大表增加带默认值的字段，需要拆分成多个步骤，否则会长时间锁表。如下图：

十多年的珠海网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。全网整合营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整珠海建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。成都创新互联公司从事“珠海网站设计”,“珠海网站推广”以来，每个客户项目都认真落实执行。

对260万数据的表加带默认值的字段，执行超过2分钟。

oracle增加字段带默认值

我们的规范做法步骤为：

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

oracle增加字段带默认值

实验准备：  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

oracle增加字段带默认值

直译为

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

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


文章题目：oracle增加字段带默认值
转载源于：http://scyanting.com/article/gopiih.html

其他资讯