Locks in Oracle

V$LOCKED_OBJECT

SELECT LO.session_id, LO.oracle_username, LO.os_user_name, 

       OBJ.object_name , OBJ.owner
  FROM V$LOCKED_OBJECT LO, 
       DBA_OBJECTS OBJ
WHERE LO.object_id = OBJ.object_id;

V$LOCKED_OBJECT + V$SESSION

SELECT SESS.sid, SESS.serial#, SESS.schemaname, SESS.machine, 

       SESS.program, SESS.module, SESS.blocking_session,

       LO.os_user_name, OBJ.object_name , OBJ.owner,

       SESS.sql_id,

'ALTER SYSTEM KILL SESSION ''' || SESS.sid || ',' || SESS.serial# ||''';' kill_session

  FROM V$LOCKED_OBJECT LO, 

       DBA_OBJECTS OBJ, 

       V$SESSION SESS

WHERE LO.object_id = OBJ.object_id

  AND LO.session_id = SESS.sid;

V$LOCK

SELECT A.sid "blocking session", 

       B.sid "blocked session"  , 

       DECODE (A.block,1, A.sid ||' is Blocking '||B.sid,'Not Blocking'),  

       A.type "Lock Type"

FROM V$LOCK A, 

     V$LOCK B 

WHERE A.sid != B.sid 

  AND A.id1 = B.id1  

  AND A.id2 = B.id2 

  AND A.block = 1

  AND B.request > 0;

V$SESSION - lock time

SELECT 

  sid, serial#, username, machine, program,

  blocking_session "blocking_session",

  sid "blocked_session",

  serial# "blocked_serial#", 

  seconds_in_wait/60 "wait_time(minutes)"

FROM V$SESSION

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session;

V$SESSION - lock time + lock info

SELECT 

  ROUND(V_SESS_LOCKED.seconds_in_wait/60) "WAIT_TIME (MIN)",

  V_SESS_LOCKED.sid       locked_sid, 

  V_SESS_LOCKED.serial#   locked_serial, 

  V_SESS_LOCKED.username  locked_username, 

  V_SESS_LOCKED.machine   locked_machine, 

  V_SESS_LOCKED.program   locked_program,

  V_SESS_LOCKED.blocking_session blocking_session,

  V_SESS_LOCKED.sql_id    locked_sql,

  V_SESS_LOCKING.sid      locking_session,

  V_SESS_LOCKING.serial#  locking_serial,

  V_SESS_LOCKING.username locking_username,

  V_SESS_LOCKING.machine  locking_machine,

  V_SESS_LOCKING.program  locking_program,

  V_SESS_LOCKING.sql_id   locking_sql

FROM V$SESSION V_SESS_LOCKED,

     V$SESSION V_SESS_LOCKING

WHERE V_SESS_LOCKED.blocking_session IS NOT NULL

  AND V_SESS_LOCKED.blocking_session = V_SESS_LOCKING.sid

ORDER BY ROUND(V_SESS_LOCKED.seconds_in_wait/60) DESC;