locked tables in oracle
-- 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 (+);