blocks|key|283692|text|您的表已被某些查询锁定。例如，您可能已经执行了"select+For+update“，但尚未提交/回滚并触发另一个select查询。在执行查询之前执行提交/回滚。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283693|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|280117|text|您的问题看起来像是混合了DML和DDL操作。请参阅此URL，它解释了此问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|280118|http://www.orafaq.com/forum/t/54714/2/|offset|length|280119|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|12|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

Your problem looks like you are mixing DML &amp; DDL operations. See this URL which explains this issue:

<a href="http://www.orafaq.com/forum/t/54714/2/" rel="nofollow noreferrer">http://www.orafaq.com/forum/t/54714/2/</a>

blocks|key|3207375|text|从这里开始ORA-00054:+resource+busy+and+acquire+with+NOWAIT+specified|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3207376|您还可以查找sql、用户名、计算机、端口信息，并进入持有连接的实际进程|3207377|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;|code-block|syntax|javascript|3207378|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3075738/ora-00054-resource-busy-and-acquire-with-nowait-specified^0|5|1M|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

from here <a href="https://stackoverflow.com/questions/3075738/ora-00054-resource-busy-and-acquire-with-nowait-specified">ORA-00054: resource busy and acquire with NOWAIT specified</a>

You can also look up the sql,username,machine,port information and get to the actual process which holds the connection

<pre><code>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;
</code></pre>

blocks|key|2993876|text|Shashi的链接给出的解决方案是最好的…不需要联系dba或其他人|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2993877|创建备份|2993878|create+table+xxxx_backup+as+select+*+from+xxxx;|code-block|syntax|javascript|2993879|删除所有行|2993880|delete+from+xxxx;
commit;|2993881|插入备份。|2993882|insert+into+xxxx+(select+*+from+xxxx_backup);
commit;|2993883|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Solution given by Shashi's link is the best... no needs to contact dba or someone else

make a backup

<pre><code>create table xxxx_backup as select * from xxxx;
</code></pre>

delete all rows

<pre><code>delete from xxxx;
commit;
</code></pre>

insert your backup.

<pre><code>insert into xxxx (select * from xxxx_backup);
commit;
</code></pre>

blocks|key|282240|text|对于这个问题，有一个非常简单的解决方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|282241|如果你在你的会话中运行10046跟踪(google+this...要解释的太多了)。您将看到，在执行任何DDL操作之前，Oracle将执行以下操作：|282242|锁表'TABLE_NAME‘无等待|282243|因此，如果另一个会话有一个打开的事务，您会得到一个错误。所以解决办法是..。请给我击鼓。在DDL之前发出你自己的锁，并省略'NO+WAIT‘。|282244|特别说明：|282245|如果你正在拆分/删除分区，oracle只会锁定分区。--所以你可以只锁定分区的子分区。|282246|所以..。以下步骤解决了该问题。|282247|282248|锁定表'TABLE+NAME'；--您将‘等待’(开发人员将其称为挂起)。直到具有打开的事务的会话提交。这是一个队列。因此，在您前面可能会有几个会话。但你不会出错的。|ordered-list-item|282249|执行DDL。然后，您的DDL将使用NO+WAIT运行一个锁。但是，您的会话已经获得了锁。所以你很好。|282250|DDL自动提交。这会释放锁。|282251|282252|当表被锁定时，DML语句将“等待”，或者开发人员将其称为“挂起”。|282253|我在从作业运行的代码中使用它来删除分区。它工作得很好。它位于一个以每秒几百次插入的速度不断插入的数据库中。没有错误。|282254|如果你想知道的话。在11g中执行此操作。我以前在10g中也这样做过。|282255|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|17|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1A|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|1C|8|@]|9|@]|A|$]]|$1|R|3|S|5|Q|7|1D|8|@]|9|@]|A|$]]|$1|T|3|U|5|Q|7|1E|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1I|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|13|$]]

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:

LOCK TABLE 'TABLE_NAME' NO WAIT

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'. 

Special Note:

if you are doing splitting/dropping partitions oracle just locks the partition. 
-- so yo can just lock the partition subpartition. 

So...
The following steps fix the problem.

<ol>
<li>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. </li>
<li>Execute DDL. Your DDL will then run a lock with the NO WAIT. However, your session has aquired the lock. So you are good.</li>
<li>DDL auto-commits. This frees the locks.</li>
</ol>

DML statements will 'wait' or as developers call it 'hang' while the table is locked. 

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.

blocks|key|3207570|text|当资源繁忙时，会发生此错误。检查查询中是否有任何引用约束。或者，甚至您在查询中提到的表也可能很忙。他们可能正在从事一些其他工作，这些工作肯定会在以下查询结果中列出：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3207571|SELECT+*+FROM+V$SESSION+WHERE+STATUS+=+'ACTIVE'|code-block|syntax|javascript|3207572|找到SID，|3207573|SELECT+*+FROM+V$OPEN_CURSOR+WHERE+SID+=+--the+id|3207574|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'
</code></pre>

Find the SID, 

<pre><code>SELECT * FROM V$OPEN_CURSOR WHERE SID = --the id
</code></pre>

blocks|key|283823|text|当不是用于更改表的会话由于DML+(update/delete/insert)而持有锁时，就会发生这种情况。如果你正在开发一个新的系统，很可能是你或者你的团队中的某个人发出了update语句，你可能会终止会话而不会有太大的后果。或者，一旦知道谁打开了会话，就可以从该会话中提交。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283824|如果您有权访问SQL管理系统，请使用它来查找违规会话。也许还会杀了它。|283825|您可以使用v$session和v$lock等，但我建议您搜索如何找到该会话，然后如何终止它。|283826|在生产系统中，这真的取决于。对于oracle+10g和更早的版本，您可以执行|283827|LOCK+TABLE+mytable+in+exclusive+mode;
alter+table+mytable+modify+mycolumn+varchar2(5);|code-block|syntax|javascript|283828|在一个单独的会话中，但要准备好以下内容，以防耗时太长。|283829|alter+system+kill+session+'....|283830|这取决于您的系统，较旧的系统更可能不会每次都提交。这是一个问题，因为可能有很长的锁。所以你的锁将阻止任何新的锁，并等待一个谁知道什么时候会释放的锁。这就是为什么你准备好另一个语句的原因。或者，您可以寻找自动执行类似操作的PLSQL脚本。|283831|在版本11g中，有一个用于设置等待时间的新环境变量。我认为它可能会做类似于我所描述的事情。请注意，锁定问题不会消失。|283832|ALTER+SYSTEM+SET+ddl_lock_timeout=20;
alter+table+mytable+modify+mycolumn+varchar2(5);|283833|最后，最好等到系统中只有很少的用户进行这种维护时再进行维护。|283834|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|J|7|19|8|@]|9|@]|A|$K|L]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

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.

If you have access to a SQL admin system use it to find the offending session. And perhaps kill it.

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.

In a production system, it really depends. For oracle 10g and older, you could execute 

<pre><code>LOCK TABLE mytable in exclusive mode;
alter table mytable modify mycolumn varchar2(5);
</code></pre>

In a separate session but have the following ready in case it takes too long.

<pre><code>alter system kill session '....
</code></pre>

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.

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.

<pre><code>ALTER SYSTEM SET ddl_lock_timeout=20;
alter table mytable modify mycolumn varchar2(5);
</code></pre>

Finally it may be best to wait until there are few users in the system to do this kind of maintenance.

blocks|key|3207623|text|在我的例子中，我非常确定是我自己的会话阻塞了。因此，执行以下操作是安全的：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3207624|3207625|我发现了有问题的会话：|unordered-list-item|3207626|3207627|SELECT+*+FROM+V$SESSION+WHERE+OSUSER='my_local_username';|CODE|3207628|会话是inactive，但它仍然以某种方式持有锁。请注意，您可能需要在您的案例中使用其他一些WHERE条件(例如，使用上面获得的ID和SERIAL#尝试USERNAME或MACHINE+fields).|3207629|Killed会话：|3207630|3207631|alter+system+kill+session+'<id>,+<serial#>';|3207632|编辑者@thermz:如果之前的打开会话查询都不起作用，请尝试这个。此查询可帮助您在终止会话时避免语法错误：|3207633|3207634|SELECT+'ALTER+SYSTEM+KILL+SESSION+'''%7C%7CSID%7C%7C','%7C%7CSERIAL#%7C%7C'''+immediate;'+FROM+V$SESSION+WHERE+OSUSER='my_local_username_on_OS'|3207635|3207636|entityMap^0|D|6|0|0|0|0|0|1L|0|3|8|1S|2|1V|7|24|8|2D|7|0|0|0|0|18|0|0|B|0|0|0|3J|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|15|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|16|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|17|8|@]|D|@]|E|$]]|$1|K|3|L|5|6|7|18|8|@$9|19|A|1A|B|M]]|D|@]|E|$]]|$1|N|3|O|5|6|7|1B|8|@$9|1C|A|1D|B|C]|$9|1E|A|1F|B|M]|$9|1G|A|1H|B|M]|$9|1I|A|1J|B|M]|$9|1K|A|1L|B|M]]|D|@]|E|$]]|$1|P|3|Q|5|I|7|1M|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|1N|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1O|8|@$9|1P|A|1Q|B|M]]|D|@]|E|$]]|$1|U|3|V|5|6|7|1R|8|@$9|1S|A|1T|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1U|8|@]|D|@]|E|$]]|$1|X|3|Y|5|I|7|1V|8|@$9|1W|A|1X|B|M]]|D|@]|E|$]]|$1|Z|3|-4|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|10|3|-4|5|6|7|1Z|8|@]|D|@]|E|$]]]|11|$]]

In my case, I was quite sure it was one of my own sessions which was blocking. Therefore, it was safe to do the following:

<ul>
<li>I found the offending session with:

<code>SELECT * FROM V$SESSION WHERE OSUSER='my_local_username';</code>

The session was inactive, but it still held the lock somehow. Note, that you may need to use some other WHERE condition in your case (e.g. try <code>USERNAME</code> or <code>MACHINE</code> fields).</li>
<li>Killed the session using the <code>ID</code> and <code>SERIAL#</code> acquired above:

<code>alter system kill session '&lt;id&gt;, &lt;serial#&gt;';</code></li>
</ul>

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:

<ul>
<li><code>SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE OSUSER='my_local_username_on_OS'</code></li>
</ul>

blocks|key|282322|text|请终止Oracle会话|type|unstyled|depth|inlineStyleRanges|entityRanges|data|282323|使用以下查询检查活动会话信息|282324|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;|code-block|syntax|javascript|282325|像这样杀掉|282326|alter+system+kill+session+'SID,SERIAL#';|282327|(例如，alter+system+kill+session+'13,36543';)|offset|length|style|CODE|282328|参考http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html|282329|entityMap|0|LINK|mutability|MUTABLE|url|http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html^0|0|0|0|0|0|4|10|0|2|26|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|17|8|@$O|18|P|19|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@]|9|@$O|1B|P|1C|1|1D]]|A|$]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

Please Kill Oracle Session

Use below query to check active session info

<pre><code>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;
</code></pre>

kill like

<pre><code>alter system kill session 'SID,SERIAL#';
</code></pre>

(For example, <code>alter system kill session '13,36543'</code>;)

Reference
<a href="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</a>

blocks|key|2994215|text|只需检查持有会话的进程，然后将其杀死。一切都恢复正常了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2994216|下面的SQL将找到您的进程|2994217|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;|code-block|syntax|javascript|2994218|那就杀了它|2994219|ALTER+SYSTEM+KILL+SESSION+'sid,serial#'|2994220|或|2994221|我在网上找到的一些例子似乎也需要实例id，例如alter+system+kill+session+'130,620，@1'；|2994222|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Just check for process holding the session and Kill it. Its back to normal.

Below SQL will find your process

<pre><code>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;
</code></pre>

Then kill it 

<pre><code>ALTER SYSTEM KILL SESSION 'sid,serial#'
</code></pre>

OR 

some example I found online seems to need the instance id as well
alter system kill session '130,620,@1';

blocks|key|282345|text|当我简单地创建一个表时，我设法遇到了这个错误！显然，在一个还不存在的表上不存在争用问题。CREATE+TABLE语句包含引用填充良好的表的CONSTRAINT+fk_name+FOREIGN+KEY子句。我不得不：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|282346|282347|从CREATE+TABLE中删除FOREIGN+KEY子句在FK列上创建索引|unordered-list-item|282348|
|282349|Create+FK|282350|282351|entityMap^0|18|C|1X|U|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|V|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|W|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|X|8|@]|D|@]|E|$]]|$1|L|3|M|5|I|7|Y|8|@]|D|@]|E|$]]|$1|N|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

I managed to hit this error when simply creating a table! There was obviously no contention problem on a table that didn't yet exist. The <code>CREATE TABLE</code> statement contained a <code>CONSTRAINT fk_name FOREIGN KEY</code> clause referencing a well-populated table. I had to:

<ul>
<li>Remove the FOREIGN KEY clause from the CREATE TABLE statement</li>
<li>Create an INDEX on the FK column</li>
<li>Create the FK</li>
</ul>

blocks|key|280234|text|我也面临着类似的问题。程序员无需执行任何操作即可解决此错误。我通知了我的oracle+DBA团队。他们扼杀了会议，像个魔法师一样工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|280235|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I also face the similar Issue. Nothing programmer has to do to resolve this error. I informed to my oracle DBA team. They kill the session and worked like a charm.

blocks|key|3207689|text|当我有两个脚本在运行时，我发生了这个错误。我有：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3207690|3207691|使用方案用户帐户(帐户#1)直接连接的|unordered-list-item|3207692|另一个使用不同方案用户帐户(帐户#2)连接的会话，但作为第一个帐户|3207693|通过数据库链接进行连接|3207694|我以account+#1的身份运行表删除，然后以account+#1的身份创建表。我在account#2的会话中运行表更新。未提交更改。已以帐户#1重新运行表删除/创建脚本。drop+table+x命令出错。|offset|length|style|CODE|3207695|我通过在account+#2的SQL*Plus会话中运行COMMIT;解决了这个问题。|3207696|entityMap^0|0|0|0|0|0|2F|C|0|S|7|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|Y|8|@$L|Z|M|10|N|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|11|8|@$L|12|M|13|N|O]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|S|$]]

I had this error happen when I had 2 scripts I was running. I had:

<ul>
<li>A SQL*Plus session connected directly using a schema user account (account #1)</li>
<li>Another SQL*Plus session connected using a different schema user account (account #2), but connecting across a database link as the first account</li>
</ul>

I ran a table drop, then table creation as account #1.
I ran a table update on account #2's session. Did not commit changes.
Re-ran table drop/creation script as account #1. Got error on the <code>drop table x</code> command.

I solved it by running <code>COMMIT;</code> in the SQL*Plus session of account #2.

blocks|key|3207698|text|​|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3207699|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#';|code-block|syntax|javascript|3207700|3207701|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|K|8|@]|9|@]|A|$E|F]]|$1|G|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|M|8|@]|9|@]|A|$]]]|I|$]]

<div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>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#';</code></pre>
</div>
</div>

blocks|key|282366|text|正如在其他答案中提到的，此错误是由在其他会话中运行的并发DML操作引起的。这会导致Oracle无法使用默认的NOWAIT选项锁定DDL的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|282367|对于那些在数据库中没有管理员权限或不能终止/中断其他会话的用户，您还可以在DDL操作之前添加以下内容：|282368|alter+session+set+DDL_LOCK_TIMEOUT+=+30;
--Run+your+DDL+command,+e.g.:+alter+table,+etc.|code-block|syntax|javascript|282369|我在后台作业执行大型插入/更新操作的数据库中反复收到此错误，并且在会话中更改此参数允许DDL在等待锁的几秒钟后继续。|282370|有关更多信息，请参阅rshdev在this+answer、this+entry+on+oracle-base或official+docs+on+DDL_LOCK_TIMEOUT上的评论。|offset|length|282371|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/16288385/1394841|1|https://oracle-base.com/articles/11g/ddl-lock-timeout-11gr1|2|https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm#REFRN10267^0|0|0|0|0|H|B|0|T|P|1|1J|X|2|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@$M|15|N|16|1|17]|$M|18|N|19|1|1A]|$M|1B|N|1C|1|1D]]|A|$]]|$1|O|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]|W|$5|R|S|T|A|$U|X]]|Y|$5|R|S|T|A|$U|Z]]]]

As mentioned in other answers, this error is caused by concurrent DML operations running in other sessions. This causes Oracle to fail to lock the table for DDL with the default NOWAIT option.
For those without admin permissions in the database or who cannot kill/interrupt the other sessions, you can also precede your DDL operation with:
<pre><code>alter session set DDL_LOCK_TIMEOUT = 30;
--Run your DDL command, e.g.: alter table, etc.
</code></pre>
I was receiving this error repeatedly in a database with background jobs doing large insert/update operations, and altering this parameter in the session allowed the DDL to continue after a few seconds of waiting for the lock.
For further information, see the comment from rshdev on <a href="https://stackoverflow.com/a/16288385/1394841">this answer</a>, <a href="https://oracle-base.com/articles/11g/ddl-lock-timeout-11gr1" rel="noreferrer">this entry on oracle-base</a> or the <a href="https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm#REFRN10267" rel="noreferrer">official docs on DDL_LOCK_TIMEOUT</a>.

Why am I getting this database error when I update a table?

<blockquote>
 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
</blockquote>

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

数据库

为什么我在更新表的时候会出现这个数据库错误？第1行出现错误: ORA-00054:资源繁忙，获取时未指定等待或超时已过期

问ORA-00054:资源繁忙，获取时指定了NOWAIT或超时已过期
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-00054:资源繁忙，获取时指定了NOWAIT或超时已过期EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-00054:资源繁忙，获取时指定了NOWAIT或超时已过期
EN