I am working on an application built on 4 node RAC. I am applying a patch and the patching session is getting blocked.
I have used below query to find the locking SID.
select INST_ID, SID, SERIAL#, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, SECONDS_IN_WAIT from gv$session where SID='4321';
Say the output is : patching SID is 4321 running on node 2 and locking SID is 1234.
Can anyone please give some query to find from which node 1234 is locking my patching session. Say worst case that all 4 nodes have SID with 1234.
I need output in the way "1234 is locking your patching session 4321 from node N"
DBA_BLOCKERS and DBA_WAITERS don't work in RAC database instances because they rely only on GV$LOCK that has not the right data about blocking session/blocking instance. This data can be found in GV$SESSION.
Thanks for your input. For the column in BLOCKING_INSTANCE on gv$session is not giving any output. It just gives the blank result.
I was just going through GV$LOCK to check if that table is helpful. But i am not understanding what is ID1 and ID2 columns.