ORA-00054: resource busy and acquire with NOWAIT specified

[huchen]

Hello,

I googled "ORA-00054" and found one of the solutions is:

"Execute the command without the NOWAIT keyword."

Can you tell me where this setting of "NOWAIT" is?

I searched my local orant9i\network\ADMIN\ and could not find "NOWAIT"

In another words, how can I do this:
"Execute the command without the NOWAIT keyword."?

 

[SantaMufasa]

Huchen,

Let me set up this scenario for you (as it may be similar to your situation):

(From SQL*Plus Session 1):SQL> select * from x;

 Ind
  ID
----
   1
   2
   3

SQL> update x set a = a + 1;

3 rows updated.

(From SQL*Plus Session 2):SQL> drop table x;
drop table x
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified.

Notice that in neither Session 1 or Session 2 did you issue a command that explicitly used the NOWAIT keyword; yet the DROP TABLE command (along with the ALTER TABLE, and a few other DDL scenarios) have an implicit "<DDL verb> WITH NOWAIT" specification.

Without the implicit "NOWAIT" specification, your table DDL commands (e.g., DROP, ALTER, et cetera) might wait indefinitely until the sessions that created locks on rows had either done a ROLLBACK or COMMIT to their transactions.

So, if this error occurs in the future, you can use the following script to identify in-flight locks on the objects that you are trying to affect:

ttitle "Lock Listing"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "O/S|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select     owner||'.'||object_name obj
   ,oracle_username||' ('||s.status||')' oruser
   ,os_user_name osuser
   ,machine computer
   ,l.process unix
   ,''''||s.sid||','||s.serial#||'''' ss
   ,r.name rs
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from       v$locked_object l
   ,dba_objects o
   ,v$session s
   ,v$transaction t
   ,v$rollname r
where l.object_id = o.object_id
  and s.sid=l.session_id
  and s.taddr=t.addr
  and t.xidusn=r.usn
order by osuser, ss, obj
/
ttitle off
set linesize 132

When you execute the above script, the output discloses the tables and locking sessions that exist at execution time for your Oracle instance. The output looks similar to the following (which I have horizontally shortened to fit on this reply window without wrapping):

Locked Object        Oracle Username  O/S Username ...
-------------------- ---------------- -------------...
TEST.X               TEST (ACTIVE)    dhunt        ...

The actual output of the above script also includes columns named, "Machine name of Locker", "O/S Process", "SID/Serial #", "RBS Name", "Login Date/Time".


Let us know if this answers your questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]