SQL ORA-00054: 资源繁忙并使用指定的 NOWAIT 获取
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3075738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00054: resource busy and acquire with NOWAIT specified
提问by wowrt
I killed a script that was in the middle of updating a table. Now when I rerun the script I am getting,
我杀死了一个正在更新表的脚本。现在，当我重新运行脚本时，
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00054: 资源繁忙并使用指定的 NOWAIT 获取
I presume the table is locked?. How do I unlock the table?. Thanks in advance.
我认为桌子被锁定了？。如何解锁桌子？提前致谢。
回答by chyee
Step 1:
第1步：
select object_name, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;
Step 2:
第2步：
alter system kill session 'sid,serial#'; --`sid` and `serial#` get from step 1
More info: http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php
更多信息：http: //www.oracle-base.com/articles/misc/killing-oracle-sessions.php
回答by Abey Tom
Thanks for the info user 'user712934'
感谢您的信息用户“user712934”
You can also look up the sql,username,machine,port information and get to the actual process which holds the connection
您还可以查找 sql、用户名、机器、端口信息并获取持有连接的实际进程
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
回答by Rob van Wijk
You'll have to wait. The session that was killed was in the middle of a transaction and updated lots of records. These records have to be rollbacked and some background process is taking care of that. In the meantime you cannot modify the records that were touched.
你必须等一下。被终止的会话在事务中间并更新了大量记录。这些记录必须回滚，一些后台进程正在处理这些。在此期间，您无法修改被触及的记录。
回答by Jeffrey Kemp
When you killed the session, the session hangs around for a while in "KILLED" status while Oracle cleans up after it.
当您终止会话时，会话会以“KILLED”状态挂起一段时间，而 Oracle 会在它之后进行清理。
If you absolutely must, you can kill the OS process as well (look up v$process.spid), which would release any locks it was holding on to.
如果您绝对必须，您也可以终止操作系统进程（查找v$process.spid），这将释放它持有的任何锁。
See thisfor more detailed info.
有关更多详细信息，请参阅此内容。
回答by Nick Kavadias
Depending on your situation, the table being locked may just be part of a normal operation & you don't want to just kill the blocking transaction. What you want to do is have your statement wait for the other resource. Oracle 11g has DDL timeouts which can be setto deal with this.
根据您的情况，被锁定的表可能只是正常操作的一部分，您不想只是终止阻塞事务。您想要做的是让您的语句等待其他资源。Oracle 11g 有 DDL 超时可以设置来处理这个。
If you're dealing with 10g then you have to get more creative and write some PL/SQL to handle the re-try. Look at Getting around ORA-00054 in Oracle 10gThis re-runs your statement when a resource_busy exception occurs.
如果您正在处理 10g，那么您必须获得更多创意并编写一些 PL/SQL 来处理重试。查看在Oracle 10g 中绕过 ORA-00054这会在发生 resource_busy 异常时重新运行您的语句。
