Oracle database locks
Today I ran the following queries in my 2 node RAC database. Enterprise database server 11.7 linux version.
SQL> select * from dba_blockers;
no rows selected
SQL> select * from dba_waiters;
no rows selected
SQL> SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||g.sid,1) sess,s.machine,s.serial#,s.sql_id sqlid,
2 id1, id2, lmode, request, g.type, g.inst_id, seconds_in_wait
3 FROM GV$LOCK g, gv$session s
4 WHERE (id1, id2, g.type) IN
5 (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
6 and g.sid = s.sid
7 and g.inst_id = s.inst_id
order by sess desc;
SESS MACHINE SERIAL# SQLID ID1 ID2 LMODE REQUEST TY INST_ID SECONDS_IN_WAIT
------------------------------ -------------------- ---------- ------------- ---------- ---------- ---------- ---------- -- ---------- ---------------
Waiter: 6462 appserver 49027 2vzvm60fvf506 276026 0 0 6 TM 2 2078
Holder: 6492 database server 8546 276026 0 3 0 TM 1 28815
SQL> SELECT a.sql_text
FROM gv$sqltext a,
gv$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &1
ORDER BY a.piece; 2 3 4 5 6 7
Enter value for 1: 6492
old 6: AND b.sid = &1
new 6: AND b.sid = 6492
no rows selected
INST_ID SID OBJECT_OWNER OBJECT_NAME LOCKED_MODE OS_USER_NAME
---------- ---------- ------------------------------ ------------------------------ ---------------------------------------- ------------------------------
1 6492 ABC XYZ Row-X (SX) oracle
2 6462 ABC XYZ None appuser
I wanted to know why dba_blockers or dba_waiters not showing blocked or waiting sessions, why no sqlid associated to holder session ?
Thanks for your time and help