| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Locks - script included
On Tue, 15 Sep 1998 7:25:53 +0200, Jeremiah Wilton wrote
(in message <Pine.OSF.3.95.980914222305.24636A-100000_at_gonzo.wolfenet.com>):
>
> On Sat, 12 Sep 1998 calberto_at_my-dejanews.com wrote:
>
>> How can I identify which user is holding(locking) resources that another
>> user wants to use? I have queried V$LOCKED_OBJECT but it shows all the
snip
> To find the blocker, someone (obviously) must be waiting on the enqueue.
> select sid from v$lock where block = 1;
>
> -- Jeremiah Wilton http://www.wolfenet.com/~jeremiah
>
Yes, this shows the waiter but not the blocker, nor the object the that is
waited for. From $ORACLE_HOME/rdbms/admin/utlockt.sql you get a tree list of
blockers and waiter for resources. In somecases lockid_1 is the object_id of
the table that's been blocked but not alway's. I now got a nice script that
shows which objects are locked, reading v$process, v$_lock, v$lock,
v$resource, sys.obj$, sys.user$ and v$session. Together with utllockt.sql this
must give a clue. I do not know if it covers all types of objects yet
(tranaction table etc.) but it will give some hint.
I got the script from oracle support. I hope it does not make this post to long but I think it will help lots of people.
REM
REM
REM Subject : List of locks by users(except MR) REM Oracle db : V7.
SET HEAD ON SET VERI OFF SET FEED OFF SET PAUSE OFF SET PAGES 66 SET RECSEP OFF SET LINES 132
BTITLE OFF TTITLE OFF
CLEAR BREAKS
CLEAR COMPUTE
CLEAR COLUMNS
CLEAR SCREEN
COL l FOR A78 TRUNC
ACCEPT us_ CHAR PROMPT "Username (LIKE format - default= all): "
TTITLE CENTER "Locks by users (except type MR) by &&us_" SKIP - RIGHT ""
COL username FOR A08 HEAD "USER OS" TRUNC
COL pid FOR 999 HEAD "PID" TRUNC
COL spid FOR A06 HEAD "SID" TRUNC
COL ora FOR A08 HEAD "USER ORA" TRUNC
COL lock FOR A10 HEAD "LOCKS" TRUNC
COL type FOR A27 HEAD "TYPE" WRAPPED
COL lmode FOR A04 HEAD "MODE" TRUNC
COL wait FOR A01 HEAD "W" TRUNC
BREAK ON username -
ON pid -
ON spid -
ON ora -
ON lock
SPOOL lockv7
SELECT p.username ,
p.pid ,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request) ,
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
AND p.username LIKE NVL('&&us_','%')
ORDER BY
1,
2,
3,
4,
5
 |
 |