SELECT (SELECT username\n FROM v$session\n WHERE SID = a.SID) blocker,\n a.SID,\n ' is blocking ',\n (SELECT username\n FROM v$session\n WHERE SID = b.SID) blockee,\n b.SID\n FROM v$lock a,\n v$lock b\n WHERE a.BLOCK = 1\n AND b.request > 0\n AND a.id1 = b.id1\n AND a.id2 = b.id2
I had to change username to osuser to get the results I needed as our software logs everybody in as the same username.
In order for Joe User to run it, I had to then do:
grant select on v_$session to gl_prod\n\ngrant select on v_$lock to gl_prodI'm not sure why it's v_$ instead of v$, but since it worked I'm not going to worry about it.