ORA-00054:资源繁忙和获取指定为NOWAIT

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.

我想桌子是锁着的吧?我怎样打开桌子?提前谢谢。

5 个解决方案

#1

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。

#2

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;

#3

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.

你必须等待。被杀死的会话位于事务的中间，并更新了大量记录。这些记录必须回滚，一些后台进程正在处理这个问题。与此同时，您不能修改被触摸的记录。

#4

When you killed the session, the session hangs around for a while in "KILLED" status while Oracle cleans up after it.

当您终止会话时，会话将在“kill”状态下挂起一段时间，而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 this for more detailed info.

有关更多详细信息，请参见本文。

#5

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 set to 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 10g This re-runs your statement when a resource_busy exception occurs.

如果您处理的是10g，那么您必须更有创造性，并编写一些PL/SQL来处理重试。看看Oracle 10g中的ola -00054吧，当出现resource_busy异常时，它会重新运行您的语句。

#1