88 lines
3.3 KiB
MySQL
88 lines
3.3 KiB
MySQL
|
@@header
|
||
|
|
|
||
|
|
/*
|
||
|
|
|
||
|
|
Author : Vishal Gupta
|
||
|
|
Purpose : Displays currently locked objects in database
|
||
|
|
|
||
|
|
* Revision History:
|
||
|
|
* ===================
|
||
|
|
* Date Author Description
|
||
|
|
* --------- ------------ -----------------------------------------
|
||
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
||
|
|
* 25-Sep-09 Vishal Gupta Modified to use v$lock as DBA_LOCK might not
|
||
|
|
* exist in some databases if rdbms/admin/dbablock.sql
|
||
|
|
* has not been run.
|
||
|
|
*/
|
||
|
|
|
||
|
|
COLUMN Blocking HEADING "Bl'ing" FORMAT a6
|
||
|
|
COLUMN Blocked HEADING "Bl'ed" FORMAT a6
|
||
|
|
COLUMN Since FORMAT a18
|
||
|
|
COLUMN inst_id HEADING 'I#' FORMAT 99
|
||
|
|
rem COLUMN sid FORMAT 9999
|
||
|
|
COLUMN username FORMAT a15
|
||
|
|
COLUMN osuser FORMAT a11 TRUNC
|
||
|
|
COLUMN machine FORMAT a15
|
||
|
|
COLUMN lock_type FORMAT a22 TRUNC
|
||
|
|
COLUMN mode_held FORMAT a13
|
||
|
|
COLUMN mode_requested FORMAT a13
|
||
|
|
COLUMN mode_requested FORMAT a13
|
||
|
|
COLUMN owner FORMAT a15
|
||
|
|
COLUMN object_type FORMAT a10
|
||
|
|
COLUMN object_name FORMAT a25
|
||
|
|
|
||
|
|
|
||
|
|
SELECT /*+ RULE */
|
||
|
|
DECODE(lk.block,1,'Y',0,' ', lk.block) Blocking
|
||
|
|
, DECODE(s.lockwait,NULL,' ','Y') Blocked
|
||
|
|
-- , DECODE(block,
|
||
|
|
-- 0, 'Not Blocking', /* Not blocking any other processes */
|
||
|
|
-- 1, 'Blocking', /* This lock blocks other processes */
|
||
|
|
-- 2, 'Global', /* This lock is global, so we can't tell */
|
||
|
|
-- TO_CHAR(block)) blocking_others
|
||
|
|
, TO_CHAR(sysdate - (lk.ctime/24/60/60),'DD-MON-YY hh24:MI:SS') Since
|
||
|
|
, lk.inst_id
|
||
|
|
, lk.sid
|
||
|
|
, s.status
|
||
|
|
, s.username
|
||
|
|
, s.osuser
|
||
|
|
, NVL(t.name, lk.type) lock_type
|
||
|
|
, DECODE(lk.lmode,
|
||
|
|
0, 'None', /* Mon Lock equivalent */
|
||
|
|
1, 'Null', /* N */
|
||
|
|
2, 'Row-S (SS)', /* L */
|
||
|
|
3, 'Row-X (SX)', /* R */
|
||
|
|
4, 'Share', /* S */
|
||
|
|
5, 'S/Row-X (SSX)', /* C */
|
||
|
|
6, 'Exclusive', /* X */
|
||
|
|
TO_CHAR(lmode)) mode_held
|
||
|
|
, DECODE(request,
|
||
|
|
0, 'None', /* Mon Lock equivalent */
|
||
|
|
1, 'Null', /* N */
|
||
|
|
2, 'Row-S (SS)', /* L */
|
||
|
|
3, 'Row-X (SX)', /* R */
|
||
|
|
4, 'Share', /* S */
|
||
|
|
5, 'S/Row-X (SSX)', /* C */
|
||
|
|
6, 'Exclusive', /* X */
|
||
|
|
TO_CHAR(request)) mode_requested
|
||
|
|
, NVL(ob.object_type,' ') object_type
|
||
|
|
, NVL(ob.owner,' ') owner
|
||
|
|
, NVL(ob.object_name,' ') object_name
|
||
|
|
FROM gv$lock lk
|
||
|
|
, dba_objects ob
|
||
|
|
, gv$session s
|
||
|
|
, v$lock_type t
|
||
|
|
WHERE lk.inst_id = s.inst_id
|
||
|
|
AND lk.sid = s.sid
|
||
|
|
AND lk.id1 = ob.object_id (+)
|
||
|
|
AND lk.type = t.type (+)
|
||
|
|
AND lk.type NOT IN
|
||
|
|
(
|
||
|
|
'MR' -- Media Recovery
|
||
|
|
, 'AE' -- Edition Lock
|
||
|
|
)
|
||
|
|
-- AND (lk.block = 1 OR s.lockwait IS NOT NULL )
|
||
|
|
ORDER BY lk.block desc, s.lockwait desc , s.sid, ob.object_name
|
||
|
|
/
|
||
|
|
|
||
|
|
@@footer
|