Answers for "locked objects in oracle"

oracle locked objects

-- Oracle: Locked objects
SELECT vs.USERNAME, vs.OSUSER, vh.SID LOCKING_SID, vs.STATUS STATUS,
       vs.MODULE MODULE, vs.PROGRAM PROGRAM_HOLDING, jrh.JOB_NAME, vsw.USERNAME,
       vsw.OSUSER, vw.SID WAITER_SID, vsw.PROGRAM PROGRAM_WAITING, jrw.JOB_NAME
FROM V$LOCK vh,
     V$LOCK vw,
     V$SESSION vs,
     V$SESSION vsw,
     DBA_SCHEDULER_RUNNING_JOBS jrh,
     DBA_SCHEDULER_RUNNING_JOBS jrw
WHERE (vh.ID1,
       vh.ID2) IN (
          SELECT ID1, ID2
          FROM V$LOCK
          WHERE REQUEST = 0
          INTERSECT
          SELECT ID1, ID2
          FROM V$LOCK
          WHERE LMODE = 0
      )
  AND vh.ID1 = vw.ID1
  AND vh.ID2 = vw.ID2
  AND vh.REQUEST = 0
  AND vw.LMODE = 0
  AND vh.SID = vs.SID
  AND vw.SID = vsw.SID
  AND vh.SID = jrh.SESSION_ID (+)
  AND vw.SID = jrw.SESSION_ID (+);

oracle locked objects

SELECT (SELECT USERNAME FROM v$session WHERE SID = a.SID) AS blocker,
       a. SID,
       ' is blocking ',
       (SELECT USERNAME FROM v$session WHERE SID = b. SID) AS blockee,
       b. SID
FROM v$lock a,
     v$lock b
WHERE a.BLOCK = 1 AND b.REQUEST > 0 AND a.ID1 = b.ID1 AND a.ID2 = b.ID2;