Re: Any good monitor script for LOCKED object V$LOCK

From: Vincent Ventrone <moe_at_nospam.com>
Date: Thu, 16 Jun 2005 11:16:56 -0400
Message-ID: <d8s558$251$1@courier.brandeis.edu>

This query helps me:

column object format A25 heading 'DB Object' column type format A10 heading 'Obj Type' column term format A12 heading 'OS User' column lcker format A12 heading 'Oracle ID' column prog format A20 heading 'Client program'

set linesize 150
set pagesize 58
break on id1 skip 1 dup

SELECT

b.OWNER||'.'||b.OBJECT_NAME object,
b.OBJECT_TYPE type,
a.OS_USER_NAME term,
a.ORACLE_USERNAME lcker,
a.SESSION_ID "SID",
c.SERIAL# "Serial #",
c.PROGRAM prog,

NVL(c.lockwait,'HOLDING LOCK') "Lock Wait Addr.", DECODE(a.LOCKED_MODE, 2, 'ROW SHARE',

3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode"

FROM
V$LOCKED_OBJECT A,
ALL_OBJECTS B,
V$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID
AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/ Received on Thu Jun 16 2005 - 10:16:56 CDT