Querry to find locks

Query to find table locks

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#,
       DECODE (a.locked_mode,
               1, '1 = null',
               2, '2 = Row Share',
               3, '3 = Row Exclusive',
               4, '4 = Share',
               5, '5 Share Row Exclusive',
               6, '6 = Exclusive'
              ) lock_mode,
       b.status, b.osuser, b.machine
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

sample output

OWNER           OBJECT_NAME               OBJECT_TYP    SID     SERIAL# LOCK_MODE                      STATUS     OSUSER     MACHINE
--------------- ------------------------- ---------- ------ ----------- ------------------------------ ---------- ---------- ---------------------------------------------------------------------------------------
CNVAR1          xyz                                  TABLE          22        9945 3 = Row Exclusive                         INACTIVE        A            abc
CNVAR1          xyz                                  TABLE         344         375 3 = Row Exclusive                         ACTIVE           A            abc

Query to create kill session statements for locked tables

SELECT    'alter system kill session'
       || ''''
       || b.SID
       || ','
       || b.serial#
       || ''''
       || ';'
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

sample output
alter system kill session'4884,58701';
alter system kill session'4918,62906';

Querry to find the blocking sessions in oracle

SELECT    s1.username
       || '@'
       || s1.machine
       || ' ( SID='
       || s1.SID
       || ' )  is blocking '
       || s2.username
       || '@'
       || s2.machine
       || ' ( SID='
       || s2.SID
       || ' ) ' AS blocking_status
  FROM v$lock l1, v$session s1, v$lock l2, v$session s2
 WHERE s1.SID = l1.SID
   AND s2.SID = l2.SID
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l2.id2 = l2.id2;

sample output

BLOCKING_STATUS
--------------------------------------------------------------
a@xyz ( SID=22 )  is blocking a@xyz ( SID=344 )