Oracle中使用alter table add column添加字段时，如何设置默认值且不锁定表？

Question

在Oracle数据库中，使用`ALTER TABLE ADD COLUMN`添加字段时，如何设置默认值且不锁定表？ 传统方法中，直接使用`ALTER TABLE table_name ADD column_name data_type DEFAULT value`语句会导致表锁定，影响在线业务。为避免此问题，可采用分步操作：先以`NULL`添加字段，再通过`UPDATE`结合并行提示批量更新数据，最后修改字段为非空并设置默认值。但这种方法仍可能引发锁等待。 自Oracle 12c起，新增`DEFAULT ON NULL`选项和在线DDL功能，支持无锁添加带默认值的字段。例如：`ALTER TABLE table_name ADD column_name data_type DEFAULT value NOT NULL`，配合` Modifications must be online`，可实现不停机变更，显著提升可用性。这一改进对高并发系统尤为重要。

Answer 1

1. 问题概述

在Oracle数据库中，使用`ALTER TABLE ADD COLUMN`语句为表添加新字段时，如何设置默认值且不锁定表？这是一个常见的技术问题，尤其是在高并发的生产环境中。传统方法虽然可以实现这一目标，但可能会引发锁等待或影响在线业务。

本章将从以下几个方面展开讨论：

• 传统方法的局限性
• Oracle 12c引入的新特性
• 实际应用场景的重要性

例如，在一个高并发系统中，直接使用以下语句会导致表锁定：

ALTER TABLE table_name ADD column_name data_type DEFAULT value;

2. 传统方法及其局限性

为了规避表锁定的问题，传统的解决方案通常采用分步操作：

1. 首先，以`NULL`值添加新字段：

ALTER TABLE table_name ADD column_name data_type;

这一步不会锁定表，但字段初始值为`NULL`。

1. 其次，通过`UPDATE`语句结合并行提示批量更新数据：

UPDATE /*+ PARALLEL(table_name, 8) */ table_name SET column_name = default_value WHERE column_name IS NULL;

此步骤可能需要多次执行，以减少单次事务的影响范围。

1. 最后，修改字段为非空并设置默认值：

ALTER TABLE table_name MODIFY column_name data_type NOT NULL;

尽管这种方法能够避免一次性锁定整个表，但仍然可能存在锁等待问题，尤其是在大规模数据集上。

3. Oracle 12c及更高版本的改进

自Oracle 12c起，数据库引入了`DEFAULT ON NULL`选项和在线DDL功能，极大地简化了无锁添加带默认值字段的过程。以下是具体实现方式：

语法元素	描述
`DEFAULT value`	指定新字段的默认值
`DEFAULT ON NULL`	确保当字段值为`NULL`时自动应用默认值
`NOT NULL`	声明字段不可为空

以下是一个完整的示例：

ALTER TABLE table_name ADD column_name data_type DEFAULT value NOT NULL;

此外，还可以通过以下命令启用在线DDL功能：

ALTER TABLE table_name MODIFY column_name data_type DEFAULT value NOT NULL ONLINE;

这些改进显著提升了高并发环境下的可用性。

4. 流程图分析

为了更直观地理解上述过程，我们可以通过流程图来展示传统方法与Oracle 12c改进后的对比：

graph TD A[开始] --> B{是否使用
Oracle 12c及以上版本} B --是--> C[使用`DEFAULT ON NULL`
和在线DDL功能] B --否--> D[以`NULL`添加字段] D --> E[批量更新数据] E --> F[设置字段为非空] C --> G[完成] F --> G[完成]

流程图清晰地展示了两种方法的差异，帮助开发者选择最适合的方案。