[link|http://asktom.oracle.com/pls/ask/f?p=4950:8:10565570903886826970::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:839412906735|Ask Tom "Questions on Locking"]
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_prod
I'm not sure why it's v_$ instead of v$, but since it worked I'm not going to worry about it.
Darrell Spice, Jr. Trendy yet complex\nPeople seek me out - though they're not sure why\n[link|http://spiceware.org/gallery/ArtisticOverpass|Artistic Overpass] [link|http://www.spiceware.org/|SpiceWare]