column holder format a12
column waiter format a12
column object format a30

SELECT waitsess.osuser || '(' || waiter.sid || ')' waiter,
	waiter.type,
	case waiter.request
		when 0 then null
		when 1 then null
		when 2 then 'row-S'
		when 3 then 'row-X'
		when 4 then 'share'
		when 5 then 'sRowX'
		when 6 then 'excl'
	end req,
	holdsess.osuser  || '(' || holder.sid || ')' holder,
	case holder.lmode
		when 0 then null
		when 1 then null
		when 2 then 'row-S'
		when 3 then 'row-X'
		when 4 then 'share'
		when 5 then 'sRowX'
		when 6 then 'excl'
	end held,
	nvl(obj.object_name, waiter.id1 || ',' || waiter.id2) object
FROM sys.v_$lock holder
JOIN sys.v_$lock waiter
	ON (
		holder.id1 = waiter.id1
	AND	holder.id2 = waiter.id2
	AND	holder.type = waiter.type
	)
JOIN sys.v_$session holdsess
	ON (holdsess.sid = holder.sid)
JOIN sys.v_$session waitsess
	ON (waitsess.sid = waiter.sid)
LEFT OUTER JOIN sys.v_$lock tmhold
	ON (tmhold.type = 'TM' and tmhold.sid = holder.sid)
LEFT OUTER JOIN sys.v_$lock tmwait
	ON (tmwait.type = 'TM'
	AND tmwait.sid = waiter.sid)
LEFT OUTER JOIN dba_objects obj
	ON (obj.object_id = tmwait.id1)
WHERE holder.request = 0
AND holder.block = 1
AND waiter.request > 0
AND nvl(tmhold.id1,0) = nvl(tmwait.id1, 0)
/
