Monday 5 December 2011

Which FND_USER is locking that table

Which FND_USER is locking that 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';

No comments:

Post a Comment