Oracle Lock Related Queries

Based on Oracle 10g/11g

-- show lock contention
-- v$session.ROW_WAIT_OBJ# = dba_objects.OBJECT_ID
-- substr('AALha7AALAAApxMAAA',1,6) <=> dbms_rowid.ROWID_OBJECT = dba_objects.DATA_OBJECT_ID
SELECT NVL ((SELECT CASE
                      WHEN l.BLOCK = 1
                        THEN 'Holder'
                      WHEN l.request > 0
                        THEN 'Waiter'
                    END
               FROM v$lock l
              WHERE lo.session_id = l.SID
                AND (   l.BLOCK = 1
                     OR l.request > 0))
           ,'Locker') ROLE
      , se.SID || ',' || se.serial# sid_serial
      ,se.blocking_session wait_for
      , o.owner || '.' || o.object_name owner_object
      ,se.username
      ,se.status
      ,se.osuser
      ,se.machine
      ,se.program
      ,se.action
      ,DECODE (o.TEMPORARY
              ,'N', DBMS_ROWID.rowid_create (1
                                            ,o.data_object_id
                                            ,se.row_wait_file#
                                            ,se.row_wait_block#
                                            ,se.row_wait_row#
                                            )
              ,'Y', NULL
              ) row_id
      ,sq.sql_text
  FROM v$locked_object lo
      ,dba_objects o
      ,v$session se
      ,v$sql sq
 WHERE lo.object_id = o.object_id
   AND lo.session_id = se.SID
   AND se.sql_address = sq.address(+)
   AND se.sql_hash_value = sq.hash_value(+);

-- sql to kill locked session
ALTER SYSTEM DISCONNECT SESSION '263,48203' IMMEDIATE;
ALTER SYSTEM KILL SESSION '276,27825' IMMEDIATE;