97 lines
3.5 KiB
MySQL
97 lines
3.5 KiB
MySQL
|
/*
|
||
|
|
|
||
|
|
Author : Vishal Gupta
|
||
|
|
Purpose : Displays blocking locks in database
|
||
|
|
|
||
|
|
* Revision History:
|
||
|
|
* ===================
|
||
|
|
* Date Author Description
|
||
|
|
* --------- ------------ --------------------------------------------------------
|
||
|
|
* 19-Oct-12 Vishal Gupta Added ClientProcess in output and changed column sizing
|
||
|
|
* 18-Apr-12 Vishal Gupta Replace with gv$session with materialize hinted
|
||
|
|
* sub-query factoring (CTE - common table extression)
|
||
|
|
* 27-Feb-12 Vishal Gupta Created
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT *********************************
|
||
|
|
PROMPT * B L O C K I N G L O C K S
|
||
|
|
PROMPT *********************************
|
||
|
|
PROMPT
|
||
|
|
|
||
|
|
COLUMN INST_ID HEADING "I#" FORMAT 99
|
||
|
|
COLUMN SID FORMAT a30
|
||
|
|
COLUMN username HEADING "UserName" FORMAT a20 TRUNC
|
||
|
|
COLUMN lock_type HEADING "Lock|type" FORMAT a4 TRUNC
|
||
|
|
COLUMN lock_Name HEADING "Lock Name" FORMAT a22 TRUNC
|
||
|
|
COLUMN lmode HEADING "Lock|Held" FORMAT a10
|
||
|
|
COLUMN request HEADING "Lock|Request" FORMAT a10
|
||
|
|
COLUMN osuser HEADING "OSUser" FORMAT a20 TRUNC
|
||
|
|
COLUMN machine HEADING "Client|Machine" FORMAT a20 TRUNC
|
||
|
|
COLUMN process HEADING "Client|Process" FORMAT a10
|
||
|
|
COLUMN program HEADING "Program" FORMAT a30 TRUNC
|
||
|
|
COLUMN event HEADING "Event" FORMAT a30 TRUNC
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
--
|
||
|
|
-- Need to use Subquery for gv$session, otherwise session is disconnected with
|
||
|
|
-- ORA-07445 [qksopCheckPropOpt] when joining dynamic performance views in heirarichal query.
|
||
|
|
--
|
||
|
|
WITH sess as
|
||
|
|
( select /*+ RULE NO_MERGE */ s1.* from gv$session s1
|
||
|
|
)
|
||
|
|
, locks as
|
||
|
|
(select /*+ RULE NO_MERGE */ *
|
||
|
|
from gv$lock where lmode = 0
|
||
|
|
--AND ( lmode = 0 OR (lmode > 0 AND block > 0))
|
||
|
|
)
|
||
|
|
SELECT /*+ RULE */
|
||
|
|
s.inst_id
|
||
|
|
, DECODE(level
|
||
|
|
,1 , s.sid || ' (Blocker)'
|
||
|
|
, ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid
|
||
|
|
)
|
||
|
|
sid
|
||
|
|
, s.status
|
||
|
|
, NVL2(lt.type,lt.type || ': ' , NULL)|| lt.name lock_name
|
||
|
|
, DECODE(l.lmode
|
||
|
|
,0,'None'
|
||
|
|
,1,'Null'
|
||
|
|
,2,'Row-S (SS)'
|
||
|
|
,3,'Row-X (SX)'
|
||
|
|
,4,'Share (S)'
|
||
|
|
,5,'S/Row-X (SSX)'
|
||
|
|
,6,'Excl (X)'
|
||
|
|
, l.lmode
|
||
|
|
) lmode
|
||
|
|
, DECODE(l.request
|
||
|
|
,0,'None'
|
||
|
|
,1,'Null'
|
||
|
|
,2,'Row-S (SS)'
|
||
|
|
,3,'Row-X (SX)'
|
||
|
|
,4,'Share (S)'
|
||
|
|
,5,'S/Row-X (SSX)'
|
||
|
|
,6,'Excl (X)'
|
||
|
|
, l.request
|
||
|
|
) request
|
||
|
|
, s.username
|
||
|
|
, s.sql_id
|
||
|
|
, s.machine
|
||
|
|
, s.osuser
|
||
|
|
, s.process
|
||
|
|
, s.program
|
||
|
|
, s.event
|
||
|
|
FROM sess s
|
||
|
|
LEFT OUTER JOIN locks l ON l.inst_id = s.inst_id AND l.sid = s.sid
|
||
|
|
LEFT OUTER JOIN v$lock_type lt ON l.type = lt.type
|
||
|
|
WHERE ( s.blocking_session IS NOT NULL
|
||
|
|
OR exists (select 1 from sess s2 where s2.blocking_instance = s.inst_id and s2.blocking_session = s.sid)
|
||
|
|
)
|
||
|
|
CONNECT BY PRIOR s.inst_id = s.blocking_instance
|
||
|
|
AND PRIOR s.sid = s.blocking_session
|
||
|
|
START WITH s.blocking_session IS NULL
|
||
|
|
;
|
||
|
|
|