-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : locks_blocking.sql | -- | CLASS : Locks | -- | PURPOSE : Query all Blocking Locks in the databases. This query will | -- | display both the user(s) holding the lock and the user(s) | -- | waiting for the lock. This script is RAC enabled. | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Blocking Locks | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES PROMPT PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | BLOCKING LOCKS (Summary) | PROMPT +------------------------------------------------------------------------+ PROMPT SET serveroutput ON FORMAT WRAPPED SET feedback OFF DECLARE CURSOR cur_BlockingLocks IS SELECT iw.instance_name AS waiting_instance , sw.status AS waiting_status , lw.sid AS waiting_sid , sw.serial# AS waiting_serial_num , sw.username AS waiting_oracle_username , sw.osuser AS waiting_os_username , sw.machine AS waiting_machine , pw.spid AS waiting_spid , SUBSTR(sw.terminal,0, 39) AS waiting_terminal , SUBSTR(sw.program,0, 39) AS waiting_program , ROUND(lw.ctime/60) AS waiting_lock_time_min , DECODE ( lh.type , 'CF', 'Control File' , 'DX', 'Distributed Transaction' , 'FS', 'File Set' , 'IR', 'Instance Recovery' , 'IS', 'Instance State' , 'IV', 'Libcache Invalidation' , 'LS', 'Log Start or Log Switch' , 'MR', 'Media Recovery' , 'RT', 'Redo Thread' , 'RW', 'Row Wait' , 'SQ', 'Sequence Number' , 'ST', 'Diskspace Transaction' , 'TE', 'Extend Table' , 'TT', 'Temp Table' , 'TX', 'Transaction' , 'TM', 'DML' , 'UL', 'PLSQL User_lock' , 'UN', 'User Name' , 'Nothing-' ) AS waiter_lock_type , DECODE ( lw.request , 0, 'None' /* Mon Lock equivalent */ , 1, 'NoLock' /* N */ , 2, 'Row-Share (SS)' /* L */ , 3, 'Row-Exclusive (SX)' /* R */ , 4, 'Share-Table' /* S */ , 5, 'Share-Row-Exclusive (SSX)' /* C */ , 6, 'Exclusive' /* X */ , '[Nothing]' ) AS waiter_mode_request , ih.instance_name AS locking_instance , sh.status AS locking_status , lh.sid AS locking_sid , sh.serial# AS locking_serial_num , sh.username AS locking_oracle_username , sh.osuser AS locking_os_username , sh.machine AS locking_machine , ph.spid AS locking_spid , SUBSTR(sh.terminal,0, 39) AS locking_terminal , SUBSTR(sh.program,0, 39) AS locking_program , ROUND(lh.ctime/60) AS locking_lock_time_min , aw.sql_text AS waiting_sql_text FROM gv$lock lw , gv$lock lh , gv$instance iw , gv$instance ih , gv$session sw , gv$session sh , gv$process pw , gv$process ph , gv$sqlarea aw WHERE iw.inst_id = lw.inst_id AND ih.inst_id = lh.inst_id AND sw.inst_id = lw.inst_id AND sh.inst_id = lh.inst_id AND pw.inst_id = lw.inst_id AND ph.inst_id = lh.inst_id AND aw.inst_id = lw.inst_id AND sw.sid = lw.sid AND sh.sid = lh.sid AND lh.id1 = lw.id1 AND lh.id2 = lw.id2 AND lh.request = 0 AND lw.lmode = 0 AND (lh.id1, lh.id2) IN ( SELECT id1,id2 FROM gv$lock WHERE request = 0 INTERSECT SELECT id1,id2 FROM gv$lock WHERE lmode = 0 ) AND sw.paddr = pw.addr (+) AND sh.paddr = ph.addr (+) AND sw.sql_address = aw.address ORDER BY iw.instance_name , lw.sid; TYPE t_BlockingLockRecord IS RECORD ( WaitingInstanceName VARCHAR2(16) , WaitingStatus VARCHAR2(8) , WaitingSid NUMBER , WaitingSerialNum NUMBER , WaitingOracleUsername VARCHAR2(30) , WaitingOSUsername VARCHAR2(30) , WaitingMachine VARCHAR2(64) , WaitingSpid VARCHAR2(12) , WaitingTerminal VARCHAR2(30) , WaitingProgram VARCHAR2(48) , WaitingLockTimeMinute NUMBER , WaiterLockType VARCHAR2(30) , WaiterModeRequest VARCHAR2(30) , LockingInstanceName VARCHAR2(16) , LockingStatus VARCHAR2(8) , LockingSid NUMBER , LockingSerialNum NUMBER , LockingOracleUsername VARCHAR2(30) , LockingOSUsername VARCHAR2(30) , LockingMachine VARCHAR2(64) , LockingSpid VARCHAR2(12) , LockingTerminal VARCHAR2(30) , LockingProgram VARCHAR2(48) , LockingLockTimeMinute NUMBER , SQLText VARCHAR2(1000) ); TYPE t_BlockingLockRecordTable IS TABLE OF t_BlockingLockRecord INDEX BY BINARY_INTEGER; v_BlockingLockArray t_BlockingLockRecordTable; v_BlockingLockRec cur_BlockingLocks%ROWTYPE; v_NumBlockingLocksIncidents BINARY_INTEGER := 0; BEGIN DBMS_OUTPUT.ENABLE(1000000); OPEN cur_BlockingLocks; LOOP FETCH cur_BlockingLocks INTO v_BlockingLockRec; EXIT WHEN cur_BlockingLocks%NOTFOUND; v_NumBlockingLocksIncidents := v_NumBlockingLocksIncidents + 1; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingInstanceName := v_BlockingLockRec.waiting_instance; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingStatus := v_BlockingLockRec.waiting_status; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSid := v_BlockingLockRec.waiting_sid; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSerialNum := v_BlockingLockRec.waiting_serial_num; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOracleUsername := v_BlockingLockRec.waiting_oracle_username; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOSUsername := v_BlockingLockRec.waiting_os_username; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingMachine := v_BlockingLockRec.waiting_machine; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSpid := v_BlockingLockRec.waiting_spid; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingTerminal := v_BlockingLockRec.waiting_terminal; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingProgram := v_BlockingLockRec.waiting_program; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingLockTimeMinute := v_BlockingLockRec.waiting_lock_time_min; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterLockType := v_BlockingLockRec.waiter_lock_type; v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterModeRequest := v_BlockingLockRec.waiter_mode_request; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingInstanceName := v_BlockingLockRec.locking_instance; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingStatus := v_BlockingLockRec.locking_status; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSid := v_BlockingLockRec.locking_sid; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSerialNum := v_BlockingLockRec.locking_serial_num; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOracleUsername := v_BlockingLockRec.locking_oracle_username; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOSUsername := v_BlockingLockRec.locking_os_username; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingMachine := v_BlockingLockRec.locking_machine; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSpid := v_BlockingLockRec.locking_spid; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingTerminal := v_BlockingLockRec.locking_terminal; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingProgram := v_BlockingLockRec.locking_program; v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingLockTimeMinute := v_BlockingLockRec.locking_lock_time_min; v_BlockingLockArray(v_NumBlockingLocksIncidents).SQLText := v_BlockingLockRec.waiting_sql_text; END LOOP; CLOSE cur_BlockingLocks; DBMS_OUTPUT.PUT_LINE('Number of blocking lock incidents: ' || v_BlockingLockArray.COUNT); DBMS_OUTPUT.PUT(chr(10)); FOR RowIndex IN 1 .. v_BlockingLockArray.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Incident ' || RowIndex); DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE(' WAITING BLOCKING'); DBMS_OUTPUT.PUT_LINE(' ---------------------------------------- ----------------------------------------'); DBMS_OUTPUT.PUT_LINE('Instance Name : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingInstanceName, 41) || v_BlockingLockArray(RowIndex).LockingInstanceName); DBMS_OUTPUT.PUT_LINE('Oracle SID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSid, 41) || v_BlockingLockArray(RowIndex).LockingSid); DBMS_OUTPUT.PUT_LINE('Serial# : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSerialNum, 41) || v_BlockingLockArray(RowIndex).LockingSerialNum); DBMS_OUTPUT.PUT_LINE('Oracle User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOracleUsername, 41) || v_BlockingLockArray(RowIndex).LockingOracleUsername); DBMS_OUTPUT.PUT_LINE('O/S User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOSUsername, 41) || v_BlockingLockArray(RowIndex).LockingOSUsername); DBMS_OUTPUT.PUT_LINE('Machine : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingMachine, 41) || v_BlockingLockArray(RowIndex).LockingMachine); DBMS_OUTPUT.PUT_LINE('O/S PID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSpid, 41) || v_BlockingLockArray(RowIndex).LockingSpid); DBMS_OUTPUT.PUT_LINE('Terminal : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingTerminal, 41) || v_BlockingLockArray(RowIndex).LockingTerminal); DBMS_OUTPUT.PUT_LINE('Lock Time : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingLockTimeMinute || ' minutes', 41) || v_BlockingLockArray(RowIndex).LockingLockTimeMinute ||' minutes'); DBMS_OUTPUT.PUT_LINE('Status : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingStatus, 41) || v_BlockingLockArray(RowIndex).LockingStatus); DBMS_OUTPUT.PUT_LINE('Program : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingProgram, 41) || v_BlockingLockArray(RowIndex).LockingProgram); DBMS_OUTPUT.PUT_LINE('Waiter Lock Type : ' || v_BlockingLockArray(RowIndex).WaiterLockType); DBMS_OUTPUT.PUT_LINE('Waiter Mode Request : ' || v_BlockingLockArray(RowIndex).WaiterModeRequest); DBMS_OUTPUT.PUT_LINE('Waiting SQL : ' || v_BlockingLockArray(RowIndex).SQLText); DBMS_OUTPUT.PUT(chr(10)); END LOOP; END; / SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 256 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN instance_name FORMAT a9 HEADING 'Instance' COLUMN sid FORMAT 999999 HEADING 'SID' COLUMN sid_serial FORMAT a15 HEADING 'SID / Serial#' COLUMN session_status FORMAT a9 HEADING 'Status' COLUMN locking_oracle_user FORMAT a20 HEADING 'Locking Oracle User' COLUMN object_owner FORMAT a15 HEADING 'Object Owner' COLUMN object_name FORMAT a25 HEADING 'Object Name' COLUMN object_type FORMAT a15 HEADING 'Object Type' COLUMN locked_mode HEADING 'Locked Mode' CLEAR BREAKS PROMPT PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | LOCKED OBJECTS | PROMPT +------------------------------------------------------------------------+ SELECT i.instance_name instance_name , l.session_id || ' / ' || s.serial# sid_serial , s.status session_status , l.oracle_username locking_oracle_user , o.owner object_owner , o.object_name object_name , o.object_type object_type , DECODE ( l.locked_mode , 0, 'None' /* Mon Lock equivalent */ , 1, 'NoLock' /* N */ , 2, 'Row-Share (SS)' /* L */ , 3, 'Row-Exclusive (SX)' /* R */ , 4, 'Share-Table' /* S */ , 5, 'Share-Row-Exclusive (SSX)' /* C */ , 6, 'Exclusive' /* X */ , '[Nothing]' ) locked_mode FROM dba_objects o , gv$session s , gv$locked_object l , gv$instance i WHERE i.inst_id = l.inst_id AND s.inst_id = l.inst_id AND s.sid = l.session_id AND o.object_id = l.object_id ORDER BY i.instance_name , l.session_id;