ORA-00054: resource busy and acquire with NOWAIT specified 1

[coopermarsh]

Help how can i find out whats locking this

SQL> truncate table log_table;
truncate table log_table
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

 

[Dagon]

Try to lock the table with a wait option e.g.

lock table log_table in exclusive mode;

This should wait for the lock. You can then examine who is blocking you by looking at v$lock:

select * from v$lock where block <> 0;

This should give you the sid of the offending user and you can look up more details about this process from the v$session table.

 

[coopermarsh]

How long can this take, its taken over 30 mins to lock the table

 

[coopermarsh]

sry over 10 minutes

 

[Dagon]

I think you've misunderstood me. The point is that the command will wait for a lock, which means you can go in on another session and query the lock tables to find out who has got the lock. The command will never complete precisely because the table has been locked with someone.

 

[coopermarsh]

QL> select * from v$lock where block <> 0;
select * from v$lock where block <> 0
*
ERROR at line 1:
ORA-00942: table or view does not exist

 

[Dagon]

Make sure you are logged on as a DBA privileged user and try:

select * from sys.v_$lock
where block <> 0;

 

[coopermarsh]

SQL> select * from sys.v_$lock
2 where block <> 0;

ADDR KADDR SID TY ID1 ID2 LMODE
---------------- ---------------- ---------- -- ---------- ---------- ----------
REQUEST CTIME BLOCK
---------- ---------- ----------
000000041199A498 000000041199A4C0 113 TM 51793 0 3
0 7359 1

i then ran the select * from v$session where SID = 113;

retunde loads but nothing meaingful

 

[Dagon]

The columns that will help you to identify the user or process are:

username - Oracle username
osuser - operating system username
program - utility that is being run (e.g. sqlplus, TOAD)
process - OS process ID

 

[SantaMufasa]

Coopermarsh,

As a complement to Dagon's excellent suggestions, here are two scripts that I use to a) identify locks and b) to identify who is blocking whom when one process is blocking another from progressing due to lock issues. (I've widened the display to remove unsightly line wrap.):

Section 1 -- &quot;locks.sql&quot; (Shows current locks):

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 "Unix|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

Tue Sep 26      

                                              page    1
                                                      Lock Listing

                                                    Machine name         Unix                   RBS
Locked Object        Oracle Username  O/S Username  of Locker            Process   SID/Ser#     Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
TEST.AUDITOR         TEST (INACTIVE)  TENFOLD\dhunt TENFOLD\TFMOBILE2709 2428:2596 '7,1890'     RBS4 2006/09/25 11:41:52
TEST.S_EMP           TEST (INACTIVE)  TENFOLD\dhunt TENFOLD\TFMOBILE2709 2428:2596 '7,1890'     RBS4 2006/09/25 11:41:52

2 rows selected.

Section 2 -- &quot;lockblock.sql&quot;

Rem    Name:    LockBlock.sql
Rem    Author:  Dave Hunt
Rem
Rem    This script shows who is holding a lock that other people are
Rem    waiting for and who the waiters are.
Rem
Rem
prompt
prompt Gathering lock information...
set echo off
set feedback off
set pagesize 0
set linesize 150
ttitle off
col "Object" format a20
col "Holder" format a25
col "Waiter" format a25
col "Lock Type" format a20
prompt ...Countdown...8...
create table TempAllObjects as select * from All_Objects;
prompt ...7...
create index TempAllObjectsID on TempAllObjects (object_id);
prompt ...6...
create table TempVSession as select * from v$Session;
prompt ...5...
create index TempVSessionSID on TempVSession (sid);
prompt ...4...
create table TempVLock as select * from v$lock;
prompt ...3...
create index TempVLockSID on TempVLock(SID);
prompt ...2...
create index TempVLockID1 on TempVLock(ID1);
prompt ...1...
create index TempVLockType on TempVLock(Type);
prompt GO
prompt
select decode(count(*),0,'There are no blocking locks.',1,'There is 1 blocking lock:',
           'There are '||count(*)||' blocking locks:')
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
set pagesize 35
select distinct o.owner||'.'||o.object_name "Object"
   ,sh.osuser||':'||sh.username||'('||sh.sid||')' "Holder"
   ,sw.osuser||':'||sw.username||'('||sw.sid||')' "Waiter",
        decode(lh.lmode
           , 0, 'none'
           , 1, 'null'
           , 2, 'row share'
           , 3, 'row exclusive'
           , 4, 'share'
           , 5, 'share row exclusive'
           , 6, 'exclusive'
           ,    'unknown')  "Lock Type"
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
drop table TempAllObjects;
drop table TempVSession;
drop table TempVLock;
prompt
set feedback on

Gathering lock information...
...Countdown...8...
...7...
...6...
...5...
...4...
...3...
...2...
...1...
GO

There is 1 blocking lock:

Object               Holder                Waiter
      Lock Type
-------------------- ------------------------- 
------------------------- -------------
TEST.S_EMP           TENFOLD\dhunt:TEST(7)     TENFOLD\dhunt:DHUNT(10)   row exclusive

Section 3 -- Another helpful script, &quot;logins.sql&quot;, tells you who is logged in as both the blocker and &quot;blockee&quot;:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a14 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select     username oruser
   ,osuser osuser
   ,decode(substr(machine,1,7),'TENFOLD',
substr(machine,9),machine) computer
   ,'||s.sid||','||s.serial#||' ss
   ,status
   ,process
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
   ,program
from       v$session s
order by time
/
ttitle off

                                                                                OS
                                                                                
Process
Oracle Username      O/S Username   Computer/Terminal    SID/Ser#     STATUS   ID           Logon Date/Time     Program
-------------------- -------------- --------------------- 
------------ -------- ------------ -------------------- 
------------
                     SYSTEM         TFMOBILE2709          
'1,1'        ACTIVE   1048         2006/09/15 03:13:31  
ORACLE.EXE
                     SYSTEM         TFMOBILE2709          
'2,1'        ACTIVE   636          2006/09/15 03:13:33  
ORACLE.EXE
                     SYSTEM         TFMOBILE2709          
'3,1'        ACTIVE   1052         2006/09/15 03:13:33  
ORACLE.EXE
                     SYSTEM         TFMOBILE2709          
'4,1'        ACTIVE   1076         2006/09/15 03:13:34  
ORACLE.EXE
                     SYSTEM         TFMOBILE2709          
'5,1'        ACTIVE   1080         2006/09/15 03:13:34  
ORACLE.EXE
                     SYSTEM         TFMOBILE2709          
'6,1'        ACTIVE   1084         2006/09/15 03:13:34  
ORACLE.EXE
TEST                 TENFOLD\dhunt  TFMOBILE2709          
'7,1890'     INACTIVE 2428:2596    2006/09/25 11:41:52  
sqlplusw.exe
DHUNT                TENFOLD\dhunt  TFMOBILE2709          
'10,1264'    ACTIVE   1968:2516    2006/09/26 09:25:42  
sqlplusw.exe
DHUNT                TENFOLD\dhunt  TFMOBILE2709          
'9,1337'     ACTIVE   2816:2208    2006/09/26 09:27:32  
sqlplusw.exe

9 rows selected.
***************************************

Let us know if these scripts help you identify your &quot;culprit(s)&quot;.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]