【Oracle】ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

今天在修改表字段类型时出现错误：ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired，看报错应该是锁导致的。报错如下：

SQL> alter table a modify (x varchar2(10));alter table a modify (x varchar2(10))            *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

解决如下：

1. sys用户或者有dba权限的用户登录，查找锁等待情况。

SQL> select t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2where t1.session_id=t2.sid order by t2.logon_time;  USERNAME      SID    SERIAL# LOGON_TIM------------------------------ ---------- ---------- ---------SCOTT       27      19437 27-SEP-17SCOTT       27      19437 27-SEP-17

2. 查询等待SQL文本，查询过程中需要输入SID号。

SQL> select sql_text from v$session a,v$sqltext_with_newlines b  where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value  and a.sid=&sid order by piece;  Enter value for sid: 27old   3:   and a.sid=&sid order by piecenew   3:   and a.sid=27 order by pieceSQL_TEXT----------------------------------------------------------------select count(*) from a

3. 查询后发现是个无用的SQL，kill就可以了。

SQL> alter system kill session '27,19437';System altered.

4. 再次修改表字段属性，不在报错。

SQL> alter table a modify (x varchar2(10));Table altered.