Discovered these columns on the v$session and gv$session views:
FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION
These appear to be new in 11.2. I checked an 11.1.0.7 instance and they weren’t there. They were there in an 11.2.0.1 instance. Interestingly an early version of the 11.2 documentation didn’t have them. I have Oracle document E10820-03 on my hard drive and it didn’t have a reference to these columns, but the most current 11.2 docs do.
Anyway, I used these to determine which Oracle session was holding library cache locks. In earlier versions I don’t believe that the v$/gv$session views were that helpful with library cache locks. They worked with regular locks only. So, check these out when you have a locking situation and don’t know which SID is blocking the others.
Current v$session documentation here:
https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223
– Bobby
