Which User is Locking the table

Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins      fl
,fnd_user        fu
,v$locked_object vlocked
,v$process       vp
,v$session       vs
,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE ‘%’ || upper(‘&tabname_blank4all’) || ‘%’
AND nvl(vs.status,’XX’) != ‘KILLED’;