ORA-00054: resource busy and acquire with NOWAIT specified

How to check Blocking session /Locks -- oracle 10g

SELECT oracle_username || ' (' || s.osuser || ')' username, s.SID || ',' || s.serial# "SID,SESSION#"
, owner || '.' || object_name OBJECT, object_type,
DECODE( l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') status
, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode)) mode_held
FROM v$locked_object v, dba_objects d, v$lock l, v$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.SID
ORDER BY oracle_username,session_id;

To identify the locked rows, use the below query:

select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#
, row_wait_row#, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('<object_name_from_above_query>');


select name,open_mode,log_mode,host_name,database_role,to_char(startup_time,'dd/mm/yyyy hh:mm:ss')startup_time from v$database,v$instance;


SQL> -- Show all BLOCKERS and who they are blocking(BLOCKEES)...

set lines 110 wrap on echo off
 column id1 noprint
 column id2 noprint
 col TYPE format a10
 col blockedby format a10
 col Usr format a20
column minheld format 99999.9 heading "Mins|Held"

     select /*+ ORDERED */
         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
           l.sid, '         ' blockedby,
          s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
          substr(s.module,1,30) module
     from v$lock l, v$session s, v$parameter p
   where l.block > 0
     and l.sid = s.sid
     and p.name = 'parallel_server' and p.value='FALSE'
 union
  select /*+ ORDERED */
         '  Blockee' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
        l.sid, substr(to_char(b.sid),1,9) blockedby,
         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
        substr(s.module,1,30) module
    from v$lock l, v$session s, v$parameter p,
        (select sid, id1, id2 from v$lock where block > 0) b
  where l.block = 0
     and l.sid = s.sid
    and l.id1 = b.id1
     and l.id2 = b.id2
   and p.name = 'parallel_server' and p.value='FALSE'
   union
  select /*+ ORDERED */
         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
         l.sid, '         ' blockedby,
        s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
         substr(s.module,1,30) module
    from v$lock l, v$session s, v$parameter p
    where l.block > 0
     and l.sid = s.sid
     and l.lmode = 6
     and l.type = 'TX'
    and p.name = 'parallel_server' and p.value='TRUE'
  union
  select /*+ ORDERED */
         '  Blockee', rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
         l.sid, substr(to_char(b.sid),1,9) blockedby,
         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
       substr(s.module,1,30) module
  from v$lock l, v$session s, v$parameter p,
        (select bl.sid, bl.id1, bl.id2 from v$lock bl
          where bl.block > 0
            and bl.lmode = 6
            and bl.type = 'TX') b
 where l.block > 0
 and l.sid = s.sid
    and l.id1 = b.id1
    and l.id2 = b.id2
     and l.lmode = 0
     and l.type = 'TX'
      and p.name = 'parallel_server' and p.value='TRUE'
    order by 1 desc, 8, 9;
--
-- Lock Types:
--   TM(DML)
--   TX(transaction enqueue)
--   UL(user supplied)
-- Lock Modes:
--   0 None
--   1 Null(NULL)
--   2 Row-S(SS)
--   3 Row-X(SX)
--   4 Share(S)
--   5 S/Row-X(SSX)
--   6 Exclusive(X)



ALTER SYSTEM KILL SESSION 'SID,SESSION' ;

To simulate the occurrence of this error for testing/learning purposes, do as below in 2 sessions:

Session 1:
SQL> conn scott/tiger;

Connected.

SQL> create table lock_test (x number);

Table created.

SQL> insert into lock_test values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> update lock_test set x=500;

1 row updated.

-- Do not commit.

Session 2:
SQL> conn scott/tiger;

Connected.

declare y number;

begin

select x into y from lock_test for update nowait;

end;

/
ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-06512: at line 4


column username format a15
column sid format 999990 heading SID
column type format a4
column lmode format 990 heading HELD
column request format 990 heading REQ
column id1 format 99999990
column id2 format 99999990
break on id1 skip 1 dup
select
sn.username,
sn.sid,
sn.sql_id,
m.type,
decode(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'S/Row Excl',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
decode(m.request, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'S/Row Excl',
6, 'Exclusive',
request, ltrim(to_char(m.request, '990'))) request,
m.id1 id1,
m.id2 id2,
m.block,
sn.logon_time,
sn.seconds_in_wait,
sn.program
from
ops$oracle.v_session sn,
ops$oracle.v_lock m
where
(sn.sid = m.sid and m.request != 0)
or (sn.sid = m.sid and m.request = 0 and lmode != 4
and (id1,id2) in (select s.id1, s.id2 from ops$oracle.v_lock s where request != 0 and s.id1 = m.id1 and s.id2 = m.id2))