【原】ORA-00054

作者：david_zhang@sh 【转载时请以超链接形式标明文章】

链接：http://www.cnblogs.com/david-zhang-index/archive/2012/07/02/2572890.html

ORA-00054: resource busy and acquire with NOWAIT specified

翻译：ORA-00054: 资源正忙，要求指定 NOWAIT

问题如下：

1 SQL> conn scott/tiger
2 SQL> drop table student;
3 drop table student
4 
5 ORA-00054: resource busy and acquire with NOWAIT specified

解决问题思路：这个表正在被使用，有可能有lock，在dba权限下查看一下是否有locks，通过v$session，v$locked_object看看是谁锁了你想用的资源，有的话把session干掉，alter system kill session ‘sid，serial#’ 就可以了，再来drop

 1 SQL> select session_id from v$locked_object;
 2  
 3  SESSION_ID
 4  ----------
 5  92
 6  
 7  SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = 92;
 8  
 9  SID SERIAL# USERNAME OSUSER
10  ---------- ---------- ------------------------------ ------------------------------
11  92 38 SCOTT kms
12  
13  SQL> ALTER SYSTEM KILL SESSION '92,38';
14  
15  System altered
16  
17  SQL> conn scott/tiger
18  Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
19  Connected as scott
20  
21  SQL> drop table student;
22  
23  Table dropped

该类问题通用解决方案：

1 select session_id from v$locked_object; --首先得到被锁对象的session_id
2 SELECT sid, serial#, username, osuser FROM v$session where sid = session_id; --通过上面得到的session_id去取得v$session的sid和serial#，然后对该进程进行终止。
3 ALTER SYSTEM KILL SESSION 'sid,serial';
4 example:
5 ALTER SYSTEM KILL SESSION '13, 8';

 

posted @ 2012-07-02 11:15  david_zhang@sh  阅读(473)  评论(0)  编辑  收藏  举报