ORA-00054 resource busy and acquire with NOWAIT specified

The below query helps to identify the session that has locked the resource that your session is trying to lock.

You can try executing the query/job after some time (to allow the other session to complete) or kill the session that has locked the resource to proceed with this situation, use the below query to identify the session.


set linesize 140
set pages 100
col username       format a20
col "SID,SESSION#" format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
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;

Sample output:

USERNAME	SID,SESSION#	OBJECT		OBJECT_TYPE	STATUS		MODE_HELD
--------	------------	-------------	-----------	------		---------
SCOTT (oracle)	86,30		SCOTT.LOCK_TEST	TABLE		Not Blocking 	Row-X (SX)

To kill the locking session, do as below:

SQL> alter system kill session '86,30';

To identify the locked rows, use the below query:

set lines 200
col object_name for a30
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>')
;

OBJECT_NAME	ROW_WAIT_OBJ#	ROW_WAIT_FILE#	ROW_WAIT_BLOCK#	ROW_WAIT_ROW#	ROW_ID
-----------	-------------	--------------	---------------	-------------	------
LOCK_TEST	41862		4		70		0		AAAKOGAAEAAAABGAAA

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
Share

2 comments to ORA-00054 resource busy and acquire with NOWAIT specified

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>