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
