Protected by Copyscape Web Copyright Protection Software

Pages

Search This Blog

Thursday, May 30, 2013

How to know Which User is Locked the table?

SELECT do.owner
      ,do.object_name
      ,do.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlo.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 vlo
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     do
WHERE vs.sid = vlo.session_id
AND vlo.object_id = do.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 do.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';
  
Above query will give you the details of user who locked the table table.
The input parameter of this query is object name.

No comments:

Post a Comment