Which FND_USER is locking that table
Written by Anil Passi   
Friday, 09 February 2007
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 aquired 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';

Comments (3)add
...
written by Anil Passi , April 14, 2007
In that case you need to grant select priveleges to that schema, for the tables in the SQL

Thanks,
Anil
...
written by zokho , July 15, 2007
hi
in which schema i can find fnd_user table?

...
written by Anil Passi , July 15, 2007
Hi Zook

It is in applsys schema, however there is a synonym of exactly the same name in apps schema. Hence Synonym apps.fnd_user points to table applsys.fnd_user

Thanks
Anil
You must be logged in to a comment. Please register if you do not have an account yet.

busy