1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to Find list of peoples Locking AP Object and who are Online

Discussion in 'Oracle Apps Technical' started by jagadekara, Mar 20, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Query to Find list of peoples Locking AP Object

    Code (SQL):
    SELECT c.owner, c.object_name, c.object_type, fu.user_name locking_user_name,
    fl.start_time, vs.module, vs.machine, vs.osuser, vs.SID, vp.pid,
    vp.spid AS os_process_to_kill, 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 'AP%';
    -- Command to Kill Session for Releasing Lock
    ALTER SYSTEM KILL SESSION '(sid, serial#)';
    -- Check Users who are Online
    SELECT
    p.spid -- The UNIX PID
    ,s.sid ,s.serial#
    ,p.username AS os_user
    ,s.username ,s.STATUS
    ,p.terminal ,p.program
    FROM v$session s ,v$process p
    WHERE p.addr = s.paddr
    ORDER BY s.username ,p.spid ,s.sid ,s.serial# ;
    -- Locked Table Information
    SELECT
    p.spid -- The UNIX PID
    ,s.sid ,s.serial#
    ,p.username AS os_user
    ,s.username ,s.STATUS
    ,p.terminal ,p.program
    FROM v$session s ,v$process p
    WHERE p.addr = s.paddr
    ORDER BY s.username ,p.spid ,s.sid ,s.serial# ;