v$lock
set line 300
set pagesize 200
select * from v$lock;
note: type column (farther information V$LOCK_TYPE)
TX Transaction enqueue
TM DML enqueue
UL User Supplied
select type, name, DESCRIPTION from V$LOCK_TYPE where type='&TYPE_LOCK';
Lock script
vi lock.sql
set echo off
set feedback off
SELECT /*+RULE */ sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request, m.id1, m.id2
FROM gv$session sn, gv$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
Table lock
vi lockt.sqlvi pdbt.sql
SET ECHO OFF
SET FEEDBACK OFF
SELECT /*+ rule */ substr(A.ORACLE_USERNAME,1,10), d.status,C.CTIME,D.SID,D.SERIAL#,substr(B.OBJECT_NAME,1,25) "TABLA",
C.LMODE, C.type
FROM V$LOCKED_OBJECT A , DBA_OBJECTS B, V$LOCK C, V$SESSION D
WHERE B.OBJECT_ID=A.OBJECT_ID
AND A.OBJECT_ID=C.ID1
AND A.SESSION_ID=c.sid
AND A.SESSION_ID=D.SID
order by c.ctime;
SET ECHO ON
SET FEEDBACK ON
Finding Blocked OBJECT#, FILE#, BLOCK# and ROW#
select s.sid, s.event, s.row_wait_obj# obj, s.row_wait_file# row_file, s.row_wait_block# row_block, s.row_wait_row# row_row
from v$session s, v$session w
where w.blocking_session = s.sid;
buffer busy waits
enq: TX - row lock contention
