BLOCKER AND WAITER IN RAC

> First find out the blocker and waiter from both the nodes using gv$ view.


SQL> select b.inst_id,b.sid "blocker",w.inst_id,w.sid "waiter" from gv$lock b,gv$lock w where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

   INST_ID    blocker    INST_ID     waiter
---------- ---------- ---------- ------------
     1         772          2          1223


> Then find out the SID & SERIAL number with usernames.


SQL>  select username,inst_id,sid, serial# from gv$session where sid in (772,1223);

USERNAME      INST_ID     SID      SERIAL#
---------- ---------- ---------- ------------
DRAHAH          2         1223      12405
RNJUMO          1         772       5891


> Now kill the blocker by giving node id you can see here i am     killing RNJUMO user session from node 2.


SQL> alter system kill session '772,5891,@2' immediate;

System altered.

> Check the RNJUMO session status killed or not.


SQL> select username,status from v$session where username='RNJUMO';

USERNAME       STATUS
---------- ---------------------------
RNJUMO         KILLED

> Now check the database any blockers.

SQL> select b.inst_id,b.sid     "blocker",w.inst_id,w.sid "waiter" from gv$lock b,gv$lock w where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

no rows selected

                                ========= XXX ==========

Example :2

You can also find out table name which is accessed by the Blocker and Waiter:-

> First find out the Blocker & Waiter.

SQL> select b.inst_id,b.sid "blocker",w.inst_id,w.sid "waiter" from gv$lock b,gv$lock w where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

   INST_ID    blocker      INST_ID     waiter
------------------------------------------------
      1        772           1          1223

> By using Blocker & Waiter Sid find out the object id.

SQL> select inst_id,object_id from gv$locked_object where session_id in (772,1223);

   INST_ID   OBJECT_ID
-------------------------
       1      81299
       1      81299

> You can get the object name by using object_id.

SQL> select owner,object_name from dba_objects  where object_id=81299

OWNER       OBJECT_NAME
-------------------------
HMIS         AMCASDTA





Happy Learning ...