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



      ,fu.user_name locking_fnd_user_name

      ,fl.start_time locking_fnd_user_login_time







      ,vp.spid AS os_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';

Post a Comment