Currently held locks per concurrent request

select /*+ ordered */

fcr.request_id

, object_name

, object_type

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global'

) status

, decode( v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive'

, to_char(lmode)

) mode_held

from apps.fnd_concurrent_requests fcr

, gv$process pro

, gv$session sess

, gv$locked_object v

, gv$lock l

, dba_objects d

where fcr.phase_code = 'R'

and fcr.oracle_process_id = pro.spid (+)

and pro.addr = sess.paddr (+)

and sess.sid = v.session_id (+)

and v.object_id = d.object_id (+)

and v.object_id = l.id1 (+)

;