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.
