The script used to check the row lock wait problem (both standalone and rac are common) and the script rac

The script used to check the row lock wait problem (both standalone and rac are common) and the script rac

Source:

How to use historic ASH data to identify lock conflicts (Doc ID 1593227.1)

For the current lock, the script is (applicable to standalone and rac ):

with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter             from gv$session             where blocking_instance is not null               and blocking_session is not null)select lpad('  ',2*(level-1))||waiter lock_tree from (select * from lk  union all  select distinct 'root', blocker from lk  where blocker not in (select waiter from lk))connect by prior waiter=blocker start with blocker='root';

Note: The preceding statements cannot be executed in the SQL windows window of plsql-dev. They can be executed in the command windows window of plsql-dev and in sqlplus. The query result is:

That is to say, the sssion with sid 1094 of instance 1 blocks the session with sid 1077 of instance 1.

This is the situation where one session blocks one session. Next we will look at the situation where one session blocks two sessions:

That is to say, the sssion with sid 1094 of instance 1 blocks two sessions: for example 1 with a session whose sid is 1077 and a session whose sid is 1086 on instance 1, use the script of Hou shengwen (Thank you for your attention). The result is as follows: