Query To Monitor Database Locks

Download(locks.sql)

REM +======================================================================+
REM                    
REM File Name: locks.sql
REM 
REM Description:
REM   Query To Monitor Database Locks
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @locks.sql 
REM   
REM +======================================================================+

clear columns
set lines 230
set pages 100
col STATUS for a8
col MACHINE for a12 
col "SCHEMA" for a8
col EVENT for a20 WORD_WRAPPED
col ACTION for a20 WORD_WRAPPED

col client_identifier for a15 WORD_WRAPPED
col blocking_sid for a12
col b_inst for a8
col inst_id for 99999
SELECT   a.inst_id, --RAC
       b.status
     , a.SID
     , a.serial#
     , NVL (TO_CHAR (a.blocking_session), ' ') blocking_sid  --RAC
     , NVL (TO_CHAR (a.blocking_instance), ' ') b_inst --RAC
     , a.SCHEMANAME "SCHEMA"
     , a.machine
     , a.event
     , a.status "SIDSTAT"
     --, a.module

     , a.client_identifier --For R12.X
     , a.action
     , round((a.seconds_in_wait / 60)) "WAIT_MINS"     
     , a.last_call_et "LAST_CALL_ET"
     --, a.sql_id
   FROM gv$session a
     , (SELECT   inst_id
     , DECODE (request, 0, 'Blocker ', 'Waiter ') status
     , SID
     FROM gv$lock
       WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0) ORDER BY id1, request) b
  WHERE a.inst_id = b.inst_id 
  AND a.SID = b.SID;