Query to Find the USername who locked the given table

 Query to Find the USername  who locked the given table

This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.

The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has acquired a lock onto that table.

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('&tab_name_leaveblank4all') || '%'

AND nvl(vs.status

      ,'XX') != 'KILLED';