【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_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;  

USERNAME			      SID    SERIAL# LOGON_TIM
------------------------------ ---------- ---------- ---------
SCOTT				       27      19437 27-SEP-17
SCOTT				       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: 27
old   3:   and a.sid=&sid order by piece
new   3:   and a.sid=27 order by piece

SQL_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.