ora-00054

场景

今天使用 oracle 执行 SQL，遇到异常如下：

  [plaintext]
1
ora-00054:resource busy and acquire with nowait specified

错误原因

主要是因为有事务正在执行（或者事务已经被锁），所有导致执行不成功。

解决办法

一、查询有哪些锁

  [sql]
1
2
3
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;

如：

  [plaintext]
1
2
3
4
user    sid serial  logon_time  
DEVTEST	16	38867	2018-06-14 11:06:19
DEVTEST	16	38867	2018-06-14 11:06:19
DEVTEST	16	38867	2018-06-14 11:06:19

二、查询相关 sql

a.sid 的值，根据你查出的 sid 手动替换

  [sql]
1
2
3
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='16' order by piece;

如：

  [plaintext]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
"declare
    l_line varchar2(32767);
    l_done number;
    l_"
"buffer varchar2(32767) := '';
    l_lengthbuffer number := 0;
"
"    l_lengthline number := 0;
begin 
  loop 
    dbms_output."
"get_line( l_line, l_done ); 
    if (l_buffer is null) then 
 "
"     l_lengthbuffer := 0; 
    else 
      l_lengthbuffer := l"
"ength(l_buffer); 
    end if; 
    if (l_line is null) then 
"
"      l_lengthline := 0; 
    else 
      l_lengthline := leng"
"th(l_line); 
    end if; 
  exit when l_lengthbuffer + l_lengt"
hline > :maxbytes OR l_lengthbuffer + l_lengthline > 32767 OR l_
"done = 1; 
  l_buffer := l_buffer || l_line || chr(10); 
    e"
"nd loop; 
    :done := l_done; 
    :buffer := l_buffer; 
   "
" :line := l_line; 
end;"

三、kill 对应的锁

213 为 sid，1353 为对应 serial

  [sql]
1
alter system kill session '213,1353';