Query v$lock

Hello DBAs:

How do I query v$lock for dead lock detection?

I can look for a trace file and its components by looking at alert_log that we have a deadlock, but otherwise ...

Thanks, ST2000

ora-00060 in the alert log.
Deadlocks are recorded in trace files
You must rollback the remainder of the transaction that casued the deadlock (the statement which detected it is automatically rollback by oracle)



sorry for the plagerism!

Oh and...

since v$lock has SID, you may join V$LOCK with V$LOCKED_OBJECT and v$SESSION.


SELECT a.sid, a.username, a.schemaname,a.sql_address, a.terminal, SUBSTR(c.OBJECT_NAME,1,20) object_name, b.sql_text
FROM v$session a,
v$sqlarea b,
USER_OBJECTS c
WHERE a.process in ( select distinct process from v$locked_object )
AND a.sql_address = b.address
AND c.OBJECT_ID IN ( SELECT OBJECT_ID FROM v$locked_object);



-- I got this query form this forum (Plagerism again!!)
-- lock.sql - Shows User Lock Information
--
select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
DECODE(B.ID2, 0, A.OBJECT_NAME,
'Trans-'||to_char(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Lock Mode",
DECODE(B.REQUEST,0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
and B.SID = C.SID
and C.USERNAME is not null
order by B.SID, B.ID2

Hi,

Run the catblock.sql script. This will be available under
\rdbms\admin folder.

After completion of this you can either run utllockt.sql (available on the same folder)
or
select * from dba_blockers ;(lists only the blocking session id)
or
select * from dba_waiters ;(lists all the waiting session, blocking session etc.)

HTH.

Thanks.
Vijay.