Getting below error while trying to truncate a table TEST_TABLE
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
A job is trying to truncate TEST_TABLE table and failing to acquire an exclusive lock.
The reason is: some other session is holding the lock on the same object.
I tried to find out the lock details using below query, but no luck:
select a.session_id,a.oracle_username, a.os_user_name, b.owner “OBJECT OWNER”, b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
Why I was unsuccessful in finding the lock, at first place?
Reason is lock held across the instances. I am running the above query on node:1, but the lock is held on node:2
Instead of using V$LOCKED_OBJECT/V$LOCK, we have to depend upon GV$LOCKED_OBJECT/GV$LOCK view. We can use below query to get the required locking information across RAC instances:
Query to find out locks on a specifc table, across all RAC nodes:
select b.object_name, c.inst_id,c.sid,c.serial#
where a.object_id=b.object_id and a.session_id=c.sid and c.sid=d.sid and b.object_name = ‘&TABLE_NAME’;
Query To find out (only) blocking locks across RAC instances:
select distinct s1.username || ‘ at ‘ || s1.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘ at ‘ || s2.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;