oracle ORA-00054: 资源繁忙并获取指定的 NOWAIT 或超时已过期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4842765/
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 or timeout expired
提问by sun
Why am I getting this database error when I update a table?
为什么在更新表时会出现此数据库错误？
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
第 1 行的错误：ORA-00054：资源繁忙并使用指定的 NOWAIT 获取或超时已过期
回答by user258367
Your table is already locked by some query. For example, you may have executed "select for update" and have not yet committed/rollbacked and fired another select query. Do a commit/rollback before executing your query.
您的表已被某些查询锁定。例如，您可能已执行“选择更新”但尚未提交/回滚并触发另一个选择查询。在执行查询之前执行提交/回滚。
回答by Abey Tom
from here ORA-00054: resource busy and acquire with NOWAIT specified
从这里ORA-00054: 资源繁忙并使用指定的 NOWAIT 获取
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 Chan Myae Thu
Please Kill Oracle Session
请杀死 Oracle 会话
Use below query to check active session info
使用以下查询来检查活动会话信息
SELECT
O.OBJECT_NAME,
S.SID,
S.SERIAL#,
P.SPID,
S.PROGRAM,
SQ.SQL_FULLTEXT,
S.LOGON_TIME
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;
kill like
杀死喜欢
alter system kill session 'SID,SERIAL#';
(For example, alter system kill session '13,36543';)
（例如，alter system kill session '13,36543';）
Reference http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html
参考 http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html
回答by Bob
There is a very easy work around for this problem.
这个问题有一个非常简单的解决方法。
If you run a 10046 trace on your session (google this... too much to explain). You will see that before any DDL operation Oracle does the following:
如果您在会话中运行 10046 跟踪（谷歌这个......太多解释）。您将看到，在任何 DDL 操作之前，Oracle 会执行以下操作：
LOCK TABLE 'TABLE_NAME' NO WAIT
锁定表“TABLE_NAME”无需等待
So if another session has an open transaction you get an error. So the fix is... drum roll please. Issue your own lock before the DDL and leave out the 'NO WAIT'.
所以如果另一个会话有一个打开的事务，你会得到一个错误。所以修复是......请鼓卷。在 DDL 之前发出您自己的锁并省略“NO WAIT”。
Special Note:
特别说明：
if you are doing splitting/dropping partitions oracle just locks the partition. -- so yo can just lock the partition subpartition.
如果您正在拆分/删除分区，oracle 只会锁定该分区。- 所以你可以只锁定分区子分区。
So... The following steps fix the problem.
所以......以下步骤解决了这个问题。
- LOCK TABLE 'TABLE NAME'; -- you will 'wait' (developers call this hanging). until the session with the open transaction, commits. This is a queue. so there may be several sessions ahead of you. but you will NOT error out.
- Execute DDL. Your DDL will then run a lock with the NO WAIT. However, your session has aquired the lock. So you are good.
- DDL auto-commits. This frees the locks.
- 锁定表'表名'; -- 您将“等待”（开发人员称之为挂起）。直到与打开事务的会话提交。这是一个队列。所以可能有几个会议在你面前。但你不会出错。
- 执行 DDL。然后，您的 DDL 将运行带有 NO WAIT 的锁。但是，您的会话已获得锁定。所以你很好。
- DDL 自动提交。这释放了锁。
DML statements will 'wait' or as developers call it 'hang' while the table is locked.
当表被锁定时，DML 语句将“等待”或被开发人员称为“挂起”。
I use this in code that runs from a job to drop partitions. It works fine. It is in a database that is constantly inserting at a rate of several hundred inserts/second. No errors.
我在从作业运行以删除分区的代码中使用它。它工作正常。它位于以每秒数百次插入的速度不断插入的数据库中。没有错误。
if you are wondering. Doing this in 11g. I have done this in 10g before as well in the past.
如果你想知道。在 11g 中执行此操作。我以前也用 10g 做过这个。
回答by Arunchunaivendan
This error happens when the resource is busy. Check if you have any referential constraints in the query. Or even the tables that you have mentioned in the query may be busy. They might be engaged with some other job which will be definitely listed in the following query results:
当资源繁忙时会发生此错误。检查查询中是否有任何引用约束。甚至您在查询中提到的表也可能很忙。他们可能从事其他一些工作，这些工作肯定会列在以下查询结果中：
SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'
Find the SID,
找到 SID，
SELECT * FROM V$OPEN_CURSOR WHERE SID = --the id
回答by Arturo Hernandez
This happens when a session other than the one used to alter a table is holding a lock likely because of a DML (update/delete/insert). If you are developing a new system, it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence. Or you could commit from that session once you know who has the session open.
当用于更改表的会话以外的会话由于 DML（更新/删除/插入）而持有锁定时会发生这种情况。如果您正在开发一个新系统，很可能是您或您团队中的某个人发布了更新语句，您可以终止会话而不会产生太大影响。或者，一旦您知道谁打开了会话，您就可以从该会话提交。
If you have access to a SQL admin system use it to find the offending session. And perhaps kill it.
如果您有权访问 SQL 管理系统，请使用它来查找违规会话。也许杀死它。
You could use v$session and v$lock and others but I suggest you google how to find that session and then how to kill it.
您可以使用 v$session 和 v$lock 等，但我建议您使用谷歌搜索如何找到该会话，然后如何杀死它。
In a production system, it really depends. For oracle 10g and older, you could execute
在生产系统中，这确实取决于。对于 oracle 10g 及更早版本，您可以执行
LOCK TABLE mytable in exclusive mode;
alter table mytable modify mycolumn varchar2(5);
In a separate session but have the following ready in case it takes too long.
在单独的会话中，但请准备好以下内容以防花费太长时间。
alter system kill session '....
It depends on what system do you have, older systems are more likely to not commit every single time. That is a problem since there may be long standing locks. So your lock would prevent any new locks and wait for a lock that who knows when will be released. That is why you have the other statement ready. Or you could look for PLSQL scripts out there that do similar things automatically.
这取决于您拥有什么系统，较旧的系统更可能不会每次都提交。这是一个问题，因为可能存在长期锁定。所以你的锁会阻止任何新的锁，并等待一个知道什么时候会被释放的锁。这就是为什么你准备好另一个语句。或者您可以在那里寻找自动执行类似操作的 PLSQL 脚本。
In version 11g there is a new environment variable that sets a wait time. I think it likely does something similar to what I described. Mind you that locking issues don't go away.
在版本 11g 中有一个新的环境变量来设置等待时间。我认为它可能会做类似于我描述的事情。请注意，锁定问题不会消失。
ALTER SYSTEM SET ddl_lock_timeout=20;
alter table mytable modify mycolumn varchar2(5);
Finally it may be best to wait until there are few users in the system to do this kind of maintenance.
最后，最好等到系统中的用户很少时再进行此类维护。
回答by wrygiel
In my case, I was quite sure it was one of my own sessionswhich was blocking. Therefore, it was safe to do the following:
就我而言，我很确定这是我自己的一个会话被阻塞。因此，执行以下操作是安全的：
I found the offending session with:
SELECT * FROM V$SESSION WHERE OSUSER='my_local_username';The session was inactive, but it still held the lock somehow. Note, that you may need to use some other WHEREcondition in your case (e.g. try
USERNAMEorMACHINEfields).Killed the session using the
IDandSERIAL#acquired above:alter system kill session '<id>, <serial#>';
我发现了有问题的会话：
SELECT * FROM V$SESSION WHERE OSUSER='my_local_username';会话处于非活动状态，但它仍然以某种方式持有锁。请注意，您可能需要在您的案例中使用其他一些WHERE条件（例如 try
USERNAME或MACHINEfields）。使用上面获得的
ID和终止会话SERIAL#：alter system kill session '<id>, <serial#>';
Edited by @thermz:If none of the previous open-session queries work try this one. This query can help you to avoid syntax errors while killing sessions:
由@thermz 编辑：如果之前的开放会话查询均无效，请尝试此查询。此查询可以帮助您在终止会话时避免语法错误：
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE OSUSER='my_local_username_on_OS'
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE OSUSER='my_local_username_on_OS'
回答by Mathavan John
Just check for process holding the session and Kill it. Its back to normal.
只需检查持有会话的进程并杀死它。它恢复正常。
Below SQL will find your process
下面的 SQL 会找到你的进程
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;
Then kill it
然后杀了
ALTER SYSTEM KILL SESSION 'sid,serial#'
OR
或者
some example I found online seems to need the instance id as well alter system kill session '130,620,@1';
我在网上找到的一些示例似乎需要实例 ID 以及更改系统终止会话 '130,620,@1';
回答by harun ugur
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
ALTER SYSTEM KILL SESSION 'sid,serial#';回答by Shashi
Your problem looks like you are mixing DML & DDL operations. See this URL which explains this issue:
您的问题看起来像是混合了 DML 和 DDL 操作。请参阅解释此问题的 URL：
