Skip to content

Green color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow Oracle Apps Scripts arrow Which FND_USER is locking that table
We are glad to announce the launch of Forum for Customizations and Extensions. Click here to visit http://apps2fusion.com/forums This website has now moved to http://apps2fusion.com


Our OA Framework, BPEL Development & Apps DBA Trainings from USD 299 only [on weekends] . Click here for details.
Also see here fully verifiable feedbacks/testimonials


Which FND_USER is locking that table | Print |  E-mail
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