PROMPT Locks Holders and Requestors Report
PROMPT
select distinct sh.username || '(' || sh.sid || ')' "Lock holder",
ao.object_name "Object",
l.type,
decode(lmode, 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4,
'SHARE', 5, 'SHARE ROW
EXCLUSIVE', 6, 'EXCLUSIVE', '?') "Lock mode",
sr.username || '(' || sr.sid || ')' "Object requester"
from v$session sh, all_objects ao, v$lock l, v$session sr
where l.id1 = ao.object_id
and sh.sid = l.sid
and sh.lockwait is null
and sr.lockwait is not null
and l.type = 'TM'
order by 1, 2, 3;
PROMPT
PROMPT this displays a report showing the blocking users session, the locked
object, and the
PROMPT sessions requesting the object.
PROMPT Lock holder - ORACLE username concatenated with the session
identifier of the session
PROMPT maintaining the lock on the object.
PROMPT Object - The name of the locked object.
PROMPT type - Resource type. The following lock types are obtained by user
applications. Any
PROMPT process which is blocking others is likely to be holding one of the
locks listed below:
PROMPT RW - Row wait enqueue lock
PROMPT TM - DML enqueue lock
PROMPT TX - Transaction enqueue lock
PROMPT UL - User supplied lock
PROMPT Lock mode - Lock mode held [one of the following values]:
PROMPT 1 Null
PROMPT 2 Row share
PROMPT 3 Row exclusive
PROMPT 4 Share
PROMPT 5 Share row exclusive
PROMPT 6 Exclusive
PROMPT Object requester - ORACLE username concatenated with the session
identifier of the session
PROMPT requesting the object.
PROMPT
Paul O'Driscoll <podski_at_hotmail.com> wrote in message
news:37734B1F.691555C6_at_hotmail.com...
> Hello there,
>
> Oracle 7.3.4 - NT
>
> Does anyone know of any good tools or scripts for Transaction monitoring
> or Locking problem resolution for NT ?
>
> regards
>
> pod
>
>
Received on Sat Jun 26 1999 - 03:05:18 CDT
