在数据库管理与维护过程中，执行数据定义语言（DDL）操作时遇到错误是常有的事，当尝试为一张表创建索引时，系统抛出“ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”错误，尤其令人困扰，这个错误看似简单，但其背后涉及数据库核心的并发控制机制——锁，本文将深入剖析此错误的成因，提供一套系统化的诊断与排查流程，并给出多种切实可行的解决方案与最佳实践,旨在帮助数据库管理员和开发人员高效地定位并解决问题。
错误根源深度解析
要理解 ORA-00054，首先必须理解 Oracle 数据库的锁机制，当用户对数据进行操作时,数据库会通过锁来保证数据的一致性和完整性。
- DML 操作与锁：当执行 INSERT、UPDATE、DELETE 等 DML（数据操作语言）语句时，Oracle 会在被修改的行上加行级锁（TX锁），并在表上加意向锁，以防止其他会话对同一行进行冲突的修改，这些操作在一个事务中持续进行，直到事务被提交（COMMIT）或回滚（ROLLBACK），只要事务未结束,这些锁就会被一直持有。
- DDL 操作与锁：创建索引（CREATE INDEX）属于 DDL（数据定义语言）操作，DDL 操作需要修改表的数据字典，这是一个结构性变更，为了确保在变更过程中表的结构不被其他操作干扰，DDL 操作需要获取表的独占锁（Exclusive Lock, X锁），独占锁是最高级别的锁，一旦获取,任何其他会话都无法再对该表进行任何读写操作。
冲突的产生：ORA-00054 错误的本质，DDL 操作请求独占锁与当前已经持有锁的 DML 操作之间的冲突，当 CREATE INDEX 语句发出时，它会尝试获取目标表的独占锁，如果此时有其他会话正在对该表执行 DML 操作且尚未提交，这些会话持有的行级锁或表级共享锁会阻止 CREATE INDEX 获取独占锁，默认情况下，CREATE INDEX 会等待一段时间，如果超时后仍无法获取锁，数据库就会返回 ORA-00054 错误。
诊断与排查步骤
遇到此错误，首要任务是找出究竟是哪个会话在“霸占”着资源,以下是一套标准的排查流程。
第一步：识别锁定会话
我们可以通过查询动态性能视图来定位阻塞者，最核心的视图是 V$LOCK、V$SESSION 和 DBA_OBJECTS，下面是一个经过优化的查询脚本,可以帮你快速找到阻塞创建索引操作的会话信息：
SELECT
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.status,
s.last_call_et,
a.object_name,
'Blocker' AS role
FROM
v$locked_object l,
dba_objects a,
v$session s
WHERE
l.object_id = a.object_id
AND l.session_id = s.sid
AND a.object_name = 'YOUR_TABLE_NAME' -- 请替换为你要创建索引的表名（大写）
UNION ALL
SELECT
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.status,
s.last_call_et,
a.object_name,
'Waiter' AS role
FROM
v$session s,
v$access a,
v$process p
WHERE
s.sid = a.sid
AND a.object = 'YOUR_TABLE_NAME' -- 请替换为你要创建索引的表名（大写）
AND s.paddr = p.addr
AND s.wait_class != 'Idle';
执行此查询后，重点关注 role 为 'Blocker' 的记录，这条记录显示了持有锁的会话 ID（sid）、序列号（serial#）、用户名（username）、操作系统用户（osuser）以及来源机器（machine）等关键信息。
第二步：分析锁定会话
找到 sid 后，可以进一步查询该会话正在执行的 SQL 语句,以判断其业务逻辑和预期结束时间。
SELECT
s.sql_id,
q.sql_text
FROM
v$session s,
v$sqlarea q
WHERE
s.sql_id = q.sql_id
AND s.sid = &BLOCKER_SID; -- 替换为上一步找到的SID
通过分析 SQL 文本，你可以联系相关业务人员或开发人员，了解该事务的背景，是正常的长时间批处理任务，还是因代码逻辑错误或网络问题导致的“僵尸”会话。
解决方案与最佳实践
明确了问题所在后,可以根据实际情况选择不同的解决方案。
| 解决方案 | 描述 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| 等待 | 不做任何操作，等待阻塞会话自然提交或回滚。 | 简单，无风险，对业务无影响。 | 耗时不确定，可能影响项目进度。 | 阻塞事务即将结束，或DDL操作不紧急。 |
| 沟通协调 | 联系阻塞会话的用户或应用负责人，请求其手动提交或回滚事务。 | 安全，能从根源解决问题。 | 依赖人工沟通，效率可能不高。 | 知道责任人，且对方能配合操作。 |
| 终止会话 | DBA 强制终止阻塞会话。 | 快速直接，能立即释放锁。 | 高风险，会导致未提交的事务回滚，可能造成数据不一致，应用需有重试机制。 | 会话确认为异常或僵尸会话，且经过评估后可以安全终止。 |
| 使用ONLINE选项 | 使用 CREATE INDEX ... ONLINE; 语句创建索引。 |
允许 DML 操作在索引创建期间继续，极大减少对业务的影响。 | 消耗更多系统资源（CPU、I/O、UNDO），创建时间更长，在开始和结束阶段仍有短暂锁定。 | 生产环境核心表，要求高可用性，无法接受长时间锁定。 |
| 选择业务低峰期 | 在系统负载最低、业务活动最少的时间窗口执行DDL。 | 对业务影响最小，操作稳定。 | 需要协调和规划，可能不适合紧急需求。 | 可计划的维护操作，或有明确的业务低峰期。 |
核心建议：
对于生产环境，首选方案是使用 ONLINE 选项，这是 Oracle 提供的为减少 DDL 操作对 DML 影响的标准方法，虽然它会消耗更多资源并延长创建时间，但其“在线”特性对于保障业务连续性至关重要。
ONLINE 创建仍然失败（可能是在开始获取短暂锁的瞬间遇到冲突），或者因资源限制无法使用，那么就需要结合诊断、沟通、终止会话这套组合拳，在执行 ALTER SYSTEM KILL SESSION 'sid,serial#' 前，务必再三确认,并做好相应的风险预案。
相关问答FAQs
问题1：ORA-00054 错误是否只会在创建索引时出现？
解答：不是的，ORA-00054 是一个通用的锁超时错误，它并不仅限于创建索引操作，任何需要获取表级独占锁的 DDL 操作，都可能在与其他 DML 操作冲突时遇到此错误。ALTER TABLE ... ADD/MODIFY/DROP COLUMN（修改表结构）、TRUNCATE TABLE（截断表）、DROP TABLE（删除表）等，都需要获取独占锁，因此都有可能因为表中存在未提交的 DML 事务而触发 ORA-00054，其根本原因都是 DDL 对独占锁的需求与 DML 持有的锁之间的不兼容。
问题2：使用 ONLINE 选项创建索引有什么风险或限制？
解答：使用 ONLINE 选项虽然极大提升了可用性,但也存在一些风险和限制：
- 资源消耗：在线创建索引是一个资源密集型操作，它会生成大量的 Redo 和 Undo 日志，同时需要额外的临时空间来存储排序和映射数据，这会对系统的 CPU、I/O 和内存造成显著压力,可能影响其他业务的性能。
- 时间成本：通常情况下，在线创建索引所需的时间比传统方式更长，因为它需要维护一张中间表（日志表）来记录索引创建期间的 DML 变化,并在最后将它们合并到新索引中。
- 短暂的锁定阶段：尽管名为“在线”，但它并非全程无锁，在操作的开始阶段，它需要短暂获取一个共享锁来准备；在操作的结束阶段，它需要短暂获取一个排他锁来完成最终的合并和切换，如果这两个时间点恰好有长时间运行的 DML，仍然有极小的概率会遇到 ORA-00054 错误。
- 限制：对于某些特定类型的索引，例如位图连接索引或某些域索引，可能不支持
ONLINE创建，在函数或包正在执行时,也不能为该函数或包创建索引。