Saturday, August 7, 2010

Watch User is Locking the Table

I am new to the blogging world and before I really get into it, I am trying to post some of the queries that I already have .. so may be few of my initial articles may have just queries.
Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.
SELECT c.owner
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vp.spid AS os_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 fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tabname_blank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';

No comments:

Post a Comment